Ranking by multiple columns

[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-yuezhe-msft]

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

Regards,
Lydia

[Fredrik_W]

I got this to work with my multi-column ranking, but I also want the SKIP-feature to work so that when two or more values are the same (i.e. same tie), they get the same ranking value and the next value gets the next ranking when counted for the previous rows. In the example, Customers 105, 106 and 107 in Village A should all be ranked 3, and Customer 104 and 103 ranked 6 and 7 respectively. I'm a rookie in Power BI and can't really see why just adding "SKIP" as an argument in the right place in the first RANKX  doesn't solves this matter (though I have a feeling it's because the complexity in the RANKX-formula). Is there a way...?

[jaak198]

Hi Lydia, 

Apologies for my ignorance in this matter but I am just starting off however how do I actually create a new table?

Ali. 

[v-yuezhe-msft]

@jaak198,

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


Regards,
Lydia

[jaak198]

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)!

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?

Thanks for all your help so far. Appreciate it. 

Ali. 

[v-yuezhe-msft]

@jaak198,

We are not able to create new table using DAX in PowerPivot, I recommend you use Power BI Desktop instead, everything works well in Power BI Desktop.

Regards,
Lydia

[jaak198]

Ok, I will give that a go and see if I can replicate my calculations in Power BI

[jaak198]

Hi Lydia,

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?

Ali.