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

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

       

      • amilecki's avatar
        amilecki
        Frequent Visitor
        9 years ago

        v-huizhn-msftand Sean

        Basically I need a dynamic top 5 filter by quantity of product defects for my stacked column chart.

         

        I've created a simplified data set called MyData including rows of claim ID, Name, ProductLine, Product, Category, Region, Date, Quantity, and Cost.  I made ProductLine and Category slicers and a stacked column chart with Product on the axis, Date in the legend, and Cost as the value then I sort by Cost.  Quantity allows for multiple defective Products on one claim (data set of 100 claims with 124 total quantity).  All of this works nicely.

         

        Now I add the following Measure for total quantity:

        TotalQuantity = CALCULATE(SUM(MyData[Quantity]), ALLEXCEPT(MyData, MyData[ProductLine], MyData[Category]))

        I added a card for TotalQuantity which updates correctly when I toggle the slicer options.

         

        For rank, first I tried the following Column in attempt to get a 1-N rank of my Products so that later I can simplify my stacked column chart to top X:

        QuantityRankColumn = RANKX(ALL(MyData[Product]), [TotalQuantity], , DESC)

        Then I added a column chart for QuantityRankColumn by Product and this results in the count of claims (rows) for each Product, summing to 100 (total number of rows).

         

        I thought it was odd doing ALL(MyData[Product]) by not including Quantity so I changed it to ALL(MyData[Product], MyData[Quantity]) but that didn't change anything.

         

        Next, I tried the following rank Measure to see if I had better luck here than with my above rank Column:

        QuantityRankMeasure = IF(HASONEVALUE(MyData[Product]), RANKX(ALL(MyData[Product]), CALCULATE(SUM(MyData[Quantity]), ALLEXCEPT(MyData, MyData[ProductLine], MyData[Category]))))

        This results in a rank of 1 for all Products.  I also tried removing the IF statement then including the Quantity column in the ALL function but again no change away from all 1's.

         

        I've used other visualization tools and this method for Power BI seems like a lot of overcomplicated work to simply see only the top bars in a chart.  I'm open to suggestions.