RANKX Always Returns 1

[amilecki]

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.

[Sean]

@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!

[amilecki]

@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]

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]

@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.

[Sean]

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

[amilecki]

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]

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

[amilecki]

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]

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 )

[amilecki]

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

[Sean]

@amilecki

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

[amilecki]

@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]

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...

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.

[Sean · Accepted solution]

@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!