Forum Discussion
Power BI - Ranking on a Matrix Visual
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()
)
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.
can I get the demo PBIX? i really want to try