Ranking by multiple columns

Question

Hi,&nbsp;&nbsp;Below is a dummy table which summarises the data I am using.&nbsp;&nbsp;VillageCustomer codeSpendTotal spend by CustomerRankA10110015001A10120015001A10130015001A10140015001A10150015001A1021007002A1021507002A1022007002A1022507002A1031001005A1042002004A1053003003A1063003003A1073003003B201110065001B201120065001B201130065001B201140065001B201150065001B202110047002B202115047002B202120047002B202125047002B203110011005B204120012004B205130013003B206130013003B207130013003&nbsp;Now the problem that I am having is that I want to be able to rank the Customers by their total spend&nbsp;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 &amp; 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&nbsp;"dense" to "skip" then that screws up my ranking at the top with all the rank 1's.&nbsp;&nbsp;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.&nbsp;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.&nbsp;&nbsp;Apologies if such topics have already been covered but I am a "newbie".&nbsp;Ali&nbsp;

anonymous · Answer

jaak198,
Create a new table using the DAX below.
Table = SUMMARIZE(Table1,Table1[Village],Table1[Customer code],"Total spend by customer",SUM(Table1[Spend]))
Then create a new column using dax below in the new table.
Rank = 
RANKX (
    FILTER (
        'Table',
        'Table'[Village] = EARLIER ( 'Table'[Village] )
    ),
    RANKX (
        FILTER (
            'Table',
            'Table'[Village] = EARLIER ( 'Table'[Village] )
        ),
        'Table'[Total spend by Customer],
        ,
        ASC
    )
        + DIVIDE (
            RANKX (
                FILTER (
                    'Table',
                    'Table'[Village] = EARLIER ( 'Table'[Village] )
                ),
                'Table'[Customer code],
                ,
                ASC
            ),
            (
                COUNTROWS (
                    FILTER (
                        'Table',
                        'Table'[Village] = EARLIER ( 'Table'[Village] )
                    )
                )
                    + 1
            )
        )
)

&nbsp;
Regards,Lydia

jaak198 · Answer

Hi Lydia,&nbsp;&nbsp;Apologies for my ignorance in this matter but I am just starting off however how do I actually create a new table?&nbsp;Ali.&nbsp;

anonymous · Answer

jaak198,Click "New Table" as shown in the screenshot below, then apply my first formula.Regards,Lydia

jaak198 · Answer

Hi Lydia,&nbsp;Very strange, I am in Power Pivot but I don't have a "New Table" option available in the toolbar. Perhaps I have to add it in as an option?&nbsp;Ali.&nbsp;

jaak198 · Answer

Or is there a way to do this without needing to create a table? I managed to sum the sales by Village AND customer code so now I have total sales for each customer by village as a separate column. I then tried to reformat your suggested DAX formula to give me the ranking that I need and it almost did the trick (I'm sure I'm doing something wrong)!&nbsp;The problem that I have now is that while it ranks the highest spending customer as no. 1, it ranks the 2nd highest spending as no. 80. I think that's because there are 80 rows of transactions for customer 1. I have tried to add in dense into your suggested DAX in order to get it to change that 80 to 2 but I can't get it to work. Any idea where I should add in the tie breaker of dense? I assume it is dense isn't it?&nbsp;Thanks for all your help so far. Appreciate it.&nbsp;&nbsp;Ali.&nbsp;

Forum Discussion

Ranking by multiple columns

8 Replies

Related content

Duplicate ranking

Ranking by Multiple Columns

Power Query - Create column - Ranking within multiple columns

Ranking KPI card

Dynamic Ranking

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