Getting RankX to work for two categories

[StephenF]

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.

I require to be able to filter on columns that are hidden here, such as month and channel btw if that helps.

Tried this but no beuno.

Ranking by Sub Category =

    RANKX (

        FILTER(

            ALL(

                DataLake[MainCategory],

                DataLake[Client]

                ),

            DataLake[MainClashName]= MAX(DataLake[MainCategory])

            ),

            CALCULATE(SUM(DataLake[Spend]))

            )

I have had circular reference issues with other implementations of rankx too.

[amitchandak]

@StephenF , Assume spend is a measure, Try like

Rankx(allexpect(Table, Table[Main Category], Table[Client]),[spend],,desc,dense)

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures

[StephenF]

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.