Getting RankX to work for two categories

Question

Hi,My Data looks like the following and I wish to generate a column as per the rightmost value in red based on clustering on two categories, category and client based on their sum spend.&nbsp;&nbsp;I require to be able to filter on columns that are hidden here, such as month and channel btw if that helps.&nbsp;Tried this but no beuno.&nbsp;Ranking by Sub Category = &nbsp;&nbsp;&nbsp;&nbsp;RANKX (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;FILTER(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ALL(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;DataLake[MainCategory],&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;DataLake[Client]&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;DataLake[MainClashName]= MAX(DataLake[MainCategory])&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;CALCULATE(SUM(DataLake[Spend]))&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)&nbsp;I have had circular reference issues with other implementations of rankx too.&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

amitchandak · Answer

StephenF , Assume spend is a measure, Try like
Rankx(allexpect(Table, Table[Main Category], Table[Client]),[spend],,desc,dense)
&nbsp;
For Rank Refer these linkshttps://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures

stephenf · Answer

I'm getting a circular reference error on creation of this measure?

Is there a way to diagnose this? Spend is made up of subtracting one calculated column from another btw.

PS: Using just some basic in table numeric field im getting the same circular reference warning.

Forum Discussion

Getting RankX to work for two categories

2 Replies

Related content

RankX

RankX at Category level for Category/SubCategory context

Using RANKX within a category

Ranking Column by Sub Category (RANKX)

RANKX