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:

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
Community 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 )
  • Sean's avatar
    Sean
    Community Champion
    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
    Community Champion
    9 years ago

    amilecki

    The easiest way to do this - use the built-in TOPN filter as shown below

  • amilecki's avatar
    amilecki
    Frequent Visitor
    9 years ago

    Thanks Sean

     

    That works to get me a full rank.  But now when I try to use that rank as a top 5 filter for the full data stacked column chart, my data seems to change dynamically and I still see 17 products and the quantity and order change.

     

    See my output screenshots here:

    https://www.dropbox.com/s/3why9853n6ttbe8/QuantityRank.docx?dl=0

  • amilecki's avatar
    amilecki
    Frequent Visitor
    9 years ago

    Seanyou are awesome!  Both of your solutions worked but I will mark RANKX as correct since it answered my original question.  I'll use TOPN but will go back to understand RANKX.  Curious as to why all my Google searches led me to RANKX.  Thanks!

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hi All,

     

    I have a similar logic to implement such that my visual is bringing fields from different tables. I have raised this in other post:

     

    Link: https://community.powerbi.com/t5/Desktop/Showing-only-required-rows-in-Matrix-based-on-Measure-Value/m-p/461241#M214003

     

    I need to put a filter of RANKX measure which should be = 1, But the participating fields are coming from different tables (connected ones). RANKX should be based on a Measure I created in report.

     

    How can we do this. Find the detail in above link.

     

    Thanks.