Rank based on aggregate spend

[jaak198]

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	300	1500	1
A	101	400	1500	1
A	101	500	1500	1
A	102	100	700	2
A	102	150	700	2
A	102	200	700	2
A	102	250	700	2
A	103	100	100	5
A	104	200	200	4
A	105	300	300	3
A	106	300	300	3
A	107	300	300	3
B	201	1100	6500	1
B	201	1200	6500	1
B	201	1300	6500	1
B	201	1400	6500	1
B	201	1500	6500	1
B	202	1100	4700	2
B	202	1150	4700	2
B	202	1200	4700	2
B	202	1250	4700	2
B	203	1100	1100	5
B	204	1200	1200	4
B	205	1300	1300	3
B	206	1300	1300	3
B	207	1300	1300	3

Now the problem that I am having is that I want to be able to rank the Customers by their total spend and I need to be able to rank this by Village. I have managed to do this using a combination of RANKX and FILTER, however, the problem I am having is that when the total spend amounts are the same (as for customers 105, 106, 107, 205, 206 & 207) then it gives the customers the same rank. I want it to give those customers a different rank. If I change my rank setting from "dense" to "skip" then that screws up my ranking at the top with all the rank 1's. 

Ideally what I want is to be able to rank by total spend, by village, and when the spend amounts are the same to then use the customer code as the tie breaker. I don't care which of the ties get ranked above the other but just that they are given a different rank.

I have see a couple of solutions using RANKX and DIVIDE however I couldn't get them to work to give me the correct results. 

Apologies if such topics have already been covered but I am a "newbie".

Ali 

[v-sihou-msft]

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

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