Forum Discussion

Sanju_BI's avatar
Sanju_BI
Frequent Visitor
3 years ago

Power BI - Ranking on a Matrix Visual

Have an issue, I've encountered with the Rank Measure used for our Parent and Child categories in Power BI visualizations. We have a Rank Measure in place that effectively calculates the ranks for o...
Need Help
Show and Tell
Tips and Tricks
Sanju_BI's avatar
Sanju_BI
Frequent Visitor
3 years ago

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.

rubayatyasmin's avatar
rubayatyasmin
Community Champion
3 years ago

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()
)