Forum Discussion
Power BI - Ranking on a Matrix Visual
The measure you've created seems to rank the parent and child categories separately, so when you expand using the + icon symbol, which drills down into the child values from a specific parent level, the rank measure might be taking into account only the child categories and ignoring the parent category. The use of ALLSELECTED could be part of the problem, since it may be only taking into account the level you've drilled down to.
A common workaround to this issue is to include an additional condition in the measure that checks for the level of hierarchy you're at and then calculates the rank accordingly.
Try modifying your measure like this:
Rank =
VAR CurrentLevel =
SWITCH (
TRUE,
ISINSCOPE ( 'Customer GP Data'[New MF Code & Parent Name] ), 1,
ISINSCOPE ( 'Customer GP Data'[Customer Name & Code] ), 2,
BLANK ()
)
VAR ismfFiltered = (CurrentLevel = 1)
VAR isCustFiltered = (CurrentLevel = 2)
RETURN
SWITCH(
TRUE(),
AND(ismfFiltered, NOT(isCustFiltered)),
IF(
COUNTROWS('Customer GP Data') > 0,
RANKX(
FILTER(
ALL('Customer GP Data'[New MF Code & Parent Name]),
[Total Est GP] > 0
),
[Total Est GP],
,
DESC,
Dense
),
BLANK()
),
isCustFiltered,
IF(
COUNTROWS('Customer GP Data') > 0,
RANKX(
FILTER(
ALL('Customer GP Data'[Customer Name & Code]),
[Total Est GP] > 0
),
[Total Est GP],
,
DESC,
Dense
),
BLANK()
),
BLANK()
)
Please note that the ALL function has been used instead of ALLSELECTED. The reason for this is that ALLSELECTED respects the context filters, while ALL ignores any filter that might be affecting the data model, which might cause an issue when ranking.
I hope this helps!
Tried your above suggestions, makes no difference. I don't believe that the issue lies with my DAX as from the first Screenshot you can see my Rank Measure works fine when I'm using the Expand down all level hierarchy.
However just using the + icon to expand from parent level thats not showing correctly as per my second screenshot.
- rubayatyasmin3 years agoCommunity Champion
try this one.
Rank =
VAR CurrentLevel =
SWITCH(
TRUE,
ISINSCOPE('Customer GP Data'[New MF Code & Parent Name]), 1,
ISINSCOPE('Customer GP Data'[Customer Name & Code]), 2,
BLANK()
)
VAR CurrentParent = SELECTEDVALUE('Customer GP Data'[New MF Code & Parent Name])
VAR ParentRank =
IF(
CurrentLevel = 1,
RANKX(
ALL('Customer GP Data'[New MF Code & Parent Name]),
CALCULATE(SUM('Customer GP Data'[Total Est GP])),
,
DESC,
Dense
),
BLANK()
)
VAR ChildRank =
IF(
CurrentLevel = 2,
RANKX(
FILTER(
ALL('Customer GP Data'[Customer Name & Code]),
'Customer GP Data'[New MF Code & Parent Name] = CurrentParent
),
CALCULATE(SUM('Customer GP Data'[Total Est GP])),
,
DESC,
Dense
),
BLANK()
)
RETURN
SWITCH(
TRUE,
NOT(ISBLANK(ParentRank)), ParentRank,
NOT(ISBLANK(ChildRank)), ChildRank,
BLANK()
)- Sanju_BI3 years agoFrequent Visitor
Appreciate it, seems to be to the ranks are just same for all the fields.
It's showing correct ranking results for the Parent but not for the Child category.- rubayatyasmin3 years agoCommunity Champion
can I get the demo PBIX? i really want to try