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 simplified my data set to just 3 rows in a <name, value> table as follows:

 

Name   Value

A          3

B          1

C          2

 

I create a new measure as:

Total = SUM(MyData[Value])

Then create a new column as:

MyRank = RANKX(ALL(MyData[Name]), [Total])

I have tried ALL(MyData) and ALL(MyData[Value]) as the first argument with no luck.  I have also tried computing the sum within the second argument both with and without CALCULATE, again no luck.

MyRank = RANKX(ALL(MyData[Value]), CALCULATE(SUM(MyData[Value])))

I don't know how I can possibly make this example simpler and I've exhausted all my research on this.  Please help.

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:

14 Replies

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

    amilecki

    Okay you want a Rank Column

    Change your Total Measure like this...

    Name Total (MEASURE) = CALCULATE( SUM(MyData[Value]), ALLEXCEPT(MyData, MyData[Name]) )

    And then here's your Rank Column

    MyRank (COLUMN) = RANKX(ALL(MyData[Name]), [Name Total])

    BTW this should also work as a Measure

    MyRank (MEASURE) =
IF (
    HASONEVALUE ( MyData[Name] ),
    RANKX (
        ALL ( MyData[Name] ),
        CALCULATE ( SUM ( MyData[Value] ), ALLEXCEPT ( MyData, MyData[Name] ) )
    )
)

    Hope this helps! :smileyhappy:

    • amilecki's avatar
      amilecki
      Frequent Visitor
      9 years ago

      Sean

      This works on my stripped down example so thank you.  But now I'm trying to extrapolate it to my real-world application which is to look at part defects over time and show the top 10 by occurrence (count).  I'll try to keep the data simple still but imagine it with 1000+ entries over 6 months for 100+ parts.

       

      ClaimID   Month   Part  Country

      1              Jan        A      USA

      2              Jan        B      China

      3              Feb       B      Italy

      4              Mar       A     Spain

      5              Mar       A     USA

      ...

       

      I created a Total Measure:

      Total = CALCULATE(COUNTA(MyData[Product]), ALL(MyData))

      And a Rank Column:

      MyRank = RANKX(ALL(MyData[ClaimID]), [Total])

      But yet again, I get all 1's for the MyRank column.  Any suggestions here?

      • v-huizhn-msft's avatar
        v-huizhn-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        9 years ago

        Hi amilecki,

        The measure you calculated Total return the one same result, so it will return 1.

        You should use the ALLEXCPET function like the Sean posted. You can create a calculated column rather than measure.

        Total = CALCULATE(COUNTA(MyData[Product]), ALLEXCEPT(MyData,MyData[Product]))


        Then rank for them.

        If this still doesn't resolve your issue,  you'd better list the expected result for your given example.

        Best Regards,
        Angelia