Forum Discussion

alexei7's avatar
alexei7
Icon for Continued Contributor rankContinued Contributor
7 years ago
Solved

RANKX DAX column help

Hi, I'm trying to create a column that will rank Invoices across Suppliers. This needs to be a column so that I can then use this Rank column to create a cumulative total and then (hopefully a meas...
Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    7 years ago

    alexei7

     

    As a calc column,,,try

     

    Calc Column =
RANKX (
    Table1,
    CALCULATE (
        SUM ( [Invoice Amount] ),
        ALLEXCEPT ( Table1, Table1[SupplierCode] )
    ),
    ,
    DESC,
    DENSE
)
Zubair_Muhammad's avatar
Zubair_Muhammad
Icon for Community Champion rankCommunity Champion
7 years ago

alexei7

 

As a calc column,,,try

 

Calc Column =
RANKX (
    Table1,
    CALCULATE (
        SUM ( [Invoice Amount] ),
        ALLEXCEPT ( Table1, Table1[SupplierCode] )
    ),
    ,
    DESC,
    DENSE
)
  • alexei7's avatar
    alexei7
    Icon for Continued Contributor rankContinued Contributor
    7 years ago

    Thanks for your help Zubair_Muhammad

     

    Unfortunately this isn't working for me.

     

    I probably should have mentioned that my dataset includes about 30 other columns (which are not required for this calculation). I guess that might be important when using ALLEXCEPT.

     

     

    See below for dummy data with the formula you helped with:

     

    Supplier Code

    Invoice Amount

    * Rank Column

    S687255

     £             23,029,923

    4

    S241863

     £               1,100,000

    52

    S712616

     £               2,700,210

    75

    S331533

     £               3,700,210

    126

    S943796

     £                   800,000

    126

    S14822

     £                   200,000

    132

    S229347

     £                   400,000

    163

    S714313

     £               6,333,333

    168

    S18322

     £                   100,000

    214

    S69059

     £                     52,000

    215

    S628244

     £                   120,000

    230

     

    Any further ideas?

    Thanks again

    Alex

    • Zubair_Muhammad's avatar
      Zubair_Muhammad
      Icon for Community Champion rankCommunity Champion
      7 years ago

      alexei7

       

      Any number of columns shouldn't make a difference I believe.

       

      Could you share your file?

       

      Is the result below from a Table Visual in Report....Perhaps it is summing the RANKs...may be you need to mark the RANK column as "Dont summarize"

      Could you share screen shot from dataview

       

      • alexei7's avatar
        alexei7
        Icon for Continued Contributor rankContinued Contributor
        7 years ago

        Thanks so much Zubair_Muhammad - the issue was summing the RANKs, also I think the reason why my own attempts were failing.