Percentile Rank Across Hierarchies Performance Issue

Anything related to PowerPivot and DAX Formuale
Jrich321
Posts: 1
Joined: Thu Aug 22, 2019 12:49 pm

Percentile Rank Across Hierarchies Performance Issue

Postby Jrich321 » Thu Aug 22, 2019 2:02 pm

Hello,

I need some help altering my measures for better performance. Its basically a ranking measure and a max of that ranking measure to create a Rank percentile across multiple levels of hierarchy.

The measures are at the EMP level and ignore the hierarchies Leader and Supervisor. See below for levels.

Group
Leader
Supervisor
EMP


I was able to achieve this however when filtering data through slicers, performance is slow and 32 bit excel throws a memory error. I believe the issue has to do with Max Rank and the filter used below
FILTER(ALLSELECTED(Table),Table[FigGroup]=MAX(Table[Group])))

See below for all measures

Measure:
Performance=
DIVIDE(SUM(Table[Repeats]),SUM(Table[Total]))


Measure:
Rank=
IF(HASONEVALUE(Table[EMP]),
RANKX(
FILTER(
ALLSELECTED(Table),Table[Group]=max(Table[Group])
),
[Performance],
,1))


Measure:
Max Rank=
CALCULATE(MAXX(ALLSELECTED(Table[EMP]),[Rank]),
FILTER(ALLSELECTED(Table),Table[FigGroup]=MAX(Table[Group])))

Measure:
Rank Percentile=
1-(DIVIDE([Rank],[MAX Rank]))

Return to “PowerPivot/DAX”

Who is online

Users browsing this forum: No registered users and 1 guest