Forum Discussion

jaak198's avatar
jaak198
New Member
8 years ago

Ranking by multiple columns

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...
Anonymous's avatar
Anonymous
Not applicable
8 years ago

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

jaak198's avatar
jaak198
New Member
8 years ago

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. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    jaak198,

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


    Regards,
    Lydia

    • jaak198's avatar
      jaak198
      New Member
      8 years ago

      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. 

    • jaak198's avatar
      jaak198
      New Member
      8 years ago

      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. 

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        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