RankX to only a certain level

[Anonymous]

Hello! I am using RankX to determine my top customers. That formula is working really well for some applications, but poorly for others.

For instance, I have a matrix with some information about the customers and then these figures split out beneath by brand. I then want to set the table to only show the top 15 customers, by using a Top N filter on the ranks.

This method works well whent the brands aren't shown beneath. However, with the brands in the table, it is showing more items than planned because it's also giving rankings at brand level. How can I get RankX to only give me one value - a ranking at the customer level?

Right now my code is as follows, where a "Chain Parent" is a customer. adding filters on all brands don't seem to fix it.

Customer Rank = RANKX(ALLSELECTED(f_RAD[Chain Parent]),[LTM 9L Cases],,DESC)

I am Live Connected to Analysis Services, so I am running into the issue that I can't add calculated columns, unfortunately.

Thank you in advance!

Hannah

[v-shex-msft]

HI @Anonymous ,

It is impossible to use one formula to handle multiple level ranking, you need to add if statement to check current row contents level and write correspond ranking formula based on current category group.

Regards,

Xiaoxin Sheng

[Anonymous]

Thank you very much! Can you give me an example of what that might look like? I have tried adding an IF statement and couldn't get it to produce a different result.

Thank you,

Hannah

[Anonymous]

Just following up to see if you can still help here! Thanks.

[v-shex-msft · Accepted solution]

HI @Anonymous ,

You can take a look at following link to know how to use if statement to check current hierarchy level:

Clever Hierarchy Handling in DAX

Sample of ranking based on hierarchy drill down:

DAX – Ranking with Hierarchy Drill-Down

Regards,

Xiaoxin Sheng