Power BI - Ranking on a Matrix Visual

[Sanju_BI]

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 our Main Category (Parent) and its corresponding Subcategories (Child) based on the Total Est GP.

The measure works perfectly when I'm using the "Expand all down one level hierarchy" button, as it provides accurate ranks for the Main Category and its associated Subcategories.

However, I've noticed that when I attempt to expand by using the + Icon Symbol, Which drill into the Child values by expanding them from a specific Parent level, the ranks do not display the correct results.

It seems that there is a visual level problem when drilling down into the Child values from a specific Parent.
I would greatly appreciate it if someone can help.

.This Works fine when using the Expand down all level hierarchyDoes not work using + Icon Symbol

[pratyashasamal]

Hi @Sanju_BI ,
You can use INSCOPE function with RANK function to get desired output . OR try using DENSE function.

RANKX =RANKX (ALLSELECTED ( 'Product'[Test] ),    [Sales], , DESC, DENSE )
Thanks,
Pratyasha Samal

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

[Sanju_BI]

Here is my Measure, that i wrote for Ranking.
Rank =
VAR ismfFiltered = ISINSCOPE('Customer GP Data'[New MF Code & Parent Name])
VAR isCustFiltered = ISINSCOPE('Customer GP Data'[Customer Name & Code])

RETURN
SWITCH(
TRUE(),
AND(ismfFiltered, NOT(isCustFiltered)),
IF(
COUNTROWS('Customer GP Data') > 0,
RANKX(
FILTER(
ALLSELECTED('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(
ALLSELECTED('Customer GP Data'[Customer Name & Code]),
[Total Est GP] > 0
),
[Total Est GP],
,
DESC,
Dense
),
BLANK()
),
BLANK()
)

[rubayatyasmin]

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!

[Sanju_BI]

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]

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_BI]

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.

[rubayatyasmin]

can I get the demo PBIX? i really want to try 

[pratyashasamal]

@Sanju_BI  , have you created heirarchy for the parent and child columns ?
Thanks,
Pratyasha Samal
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

[Sanju_BI]

Yes!. did so.,
Customer GP Data'[New MF Code & Parent Name] - That's my Parent field
Customer GP Data'[Customer Name & Code] - That's my child field

[pratyashasamal]

Hi @Sanju_BI ,
Try to replace ALL with ALLSELECT in your dax function .
Thanks,
Pratyasha Samal
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

[Sanju_BI]

My DAX already has ALLSELECTED function, and Its giving me the Ranks as per the Parent - Child, 
the problem lies with when drilling using + Icon.

[rubayatyasmin]

Hi, @Sanju_BI 

this article should help. 

https://www.sqlbi.com/articles/introducing-the-rank-window-function-in-dax/

https://www.sqlbi.com/articles/introducing-rankx-in-dax/

[Sanju_BI]

Tried of doing this mate, but seems to be not helping!.