Forum Discussion

jaak198's avatar
jaak198
New Member
8 years ago

Rank based on aggregate spend

Hi,    Below is a dummy table which summarises the data I am using.    Village Customer code Spend Total spend by Customer Rank A 101 100 1500 1 A 101 200 1500 1 A 101 3...
v-sihou-msft's avatar
v-sihou-msft
Microsoft Employee
8 years ago

jaak198

 

I assume you create a rank measure:

 

rank =
RANKX (
    ALLEXCEPT ( Table8, Table8[Village] ),
    CALCULATE (
        SUM ( Table8[Spend] ),
        ALLEXCEPT ( Table8, Table8[Village], Table8[Customer code] )
    ),
    ,
    DESC,
    DENSE
)

 

Since your logic should be based on the Spend only, if you need to have different rank for customers with same Spend, your rank is based on customer code as well.

 

Currently, it's not possible to directly make a rank measure/column primary based one column and secondary based on another column. You may create a calculated table to group the Sales together first, then apply condition to achieve this kind of logic.

 

Regards,

  • jaak198's avatar
    jaak198
    New Member
    8 years ago

    Hi,

     

    Thanks for your solution however I received the below error message when I tried to run the calculation:

     

    A circular dependency was detected: 'Customer'[Calculated Column 1],'Customer'[Calculated Column 1],'Customer'[Calculated Column 1].