Forum Discussion

amilecki's avatar
amilecki
Frequent Visitor
9 years ago
Solved

RANKX Always Returns 1

I am trying to learn the RANKX expression using a couple examples on the web but every row returns 1.  I have found a couple suggested solutions, none of which have worked for me.   I have simplifi...
Need Help
  • Sean's avatar
    Sean
    9 years ago

    amilecki

    If you do want to use a Rank Measure in the Visual Filter you have to adjust how the sum is calculated like this...

    Rank Product = 
RANKX ( ALL(MyData[Product] ), CALCULATE ( SUM ( MyData[Quantity] ), ALLEXCEPT(MyData, MyData[Product] ) ) )

    See below...

    Hope this helps! :smileyhappy:

Sean's avatar
Sean
Icon for Community Champion rankCommunity Champion
9 years ago

Can you upload this sample file you've created to OneDrive, DropBox or similar service?

amilecki's avatar
amilecki
Frequent Visitor
9 years ago

Increased data set to 500 claims and more products and uploaded to DropBox here:

https://www.dropbox.com/s/vpajtr7dvpum2e5/PowerBI_SampleData_RANKX.xlsx?dl=0

 

Running a quick Excel pivot table, I expect to see the following:

 

Product   Quantity   Rank

P06          40             1

P25          37             2

P22          35             3

P01          30             4

P04          28             5

...

 

This way I can simplify a stacked column chart from 25 Products down to just the top 5 using Rank.

 

Thank you both for your time so far.

  • Sean's avatar
    Sean
    Icon for Community Champion rankCommunity Champion
    9 years ago

    How do you get Quantity for P06 to be 40 ? and not 18?

    • amilecki's avatar
      amilecki
      Frequent Visitor
      9 years ago

      You are right, sorry about that.  I originally had a generator spreadsheet which dynamically changed after I copy-pasted the data values into the DropBox spreadsheet.  The correct expected top 5 is:

       

      Prod Qty Rank

      P17   37   1

      P07   36   2

      P12   34   3

      P03   34   3

      P15   34   3

      • Sean's avatar
        Sean
        Icon for Community Champion rankCommunity Champion
        9 years ago

        To get the result you just posted all you need is this single Measure

        Rank Product = RANKX ( ALL(MyData[Product]), CALCULATE(sum(MyData[Quantity])),, DESC )