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. 

  • Fredrik_W's avatar
    Fredrik_W
    New Member
    8 years ago

     

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