RANKX example 3 in Mastering DAX Calculations course

[pete962]

Hi All,

I am working through the Mastering DAX calculations course and have hit a problem with one of the ranking formulas in the ‘Practical examples for using ranking functions’ video.
There’s an example in which a formula ranks the total sales for the Customer Names/Product Names combination. Here is the formula:

RANKX Example 3 =
RANKX(
CROSSJOIN( ALL( Customers[Customer Names] ), ALL( Products[Product Name] ) ),
[Total Sales], ,DESC )

As you can see from the screen shot, the function hasn’t calculated the ranks correctly…

What have I missed?

Thanks in anticipation,
Pete.

[AntrikshSharma]

Looks correct to me, Product 1 at 82K is ranked 1 for Lorens, Product 1 at 62K is Ranked 2 for Dharma

[pete962]

Hi @AntrikshSharma,

Thanks for the quick reply…This is what the expected results should be (taken from the video)…

Cheers,
Pete.

[AntrikshSharma]

@pete962 That’s strange, just checked the video and your measure is exactly the same, maybe the difference is in the Total Sales Measure? Can you share the PBIX file?

[Harsh]

Hello @pete962,

Thank You for posting your query onto the Forum.

Well the same logic and formula absolutely works fine in my file. Below is the screenshot provided for the reference -

Below are the formulas used that I’ve used to calculate the Ranking as was shown in the video -

Total Sales = 
SUMX( Sales , 
    Sales[Order Quantity] * Sales[Unit Price] ) 

RANKX - Harsh = 
RANKX(
    CROSSJOIN( ALL( Customers[Customer Names] ) , ALL( Products[Product Name] ) ) ,
[Total Sales] , , DESC )

I’m also attaching working of my PBIX file for the reference.

Hoping you find this useful and helps you to find the desired results that you’re looking for.

Thanks and Warm Regards,
Harsh

RANKX Function.pbix (661.5 KB)

[pete962]

Hi @Harsh and @AntrikshSharma,

Thanks for the suggestion and. @Harsh, I have copied your formula and pasted it into my PBI and still the same results…I have uploaded a copy of the problematic PBIX…

Thanks,
Pete.Mastering DAX Calculations.pbix (827.7 KB)

[AntrikshSharma]

@pete962 To me it looks like that the PBI file is broken, look at the below screenshot, it is not able to rank even the products.

But when I created a new file it works correctly with single column with ALL.

[pete962]

Thanks for this @AntrikshSharma, do you know if this is a common fault in a PBIX? Thanks for investigating this and confirming that I’m not going crazy

Cheers,
Pete.

[BrianJ]

@pete962

do you know if this is a common fault in a PBIX?

I’ve never seen this happen before. Great detective work by @AntrikshSharma in diagnosing the source of the problem.

• Brian

[AntrikshSharma]

@pete962 To be honest, I don’t really know.

This works in your file :

RANKX Example 3 =
RANKX ( ALL ( Products ), [Total Sales],, DESC )

This doesn’t (This made me question my DAX knowledge):

RANKX Example 3 = 
RANKX ( ALL ( Products[Product Name] ), [Total Sales],, DESC )

I am able to rank in the table:

When I created a duplicate product table, everything works, I even duplicated the calculated columns:

So it will be safe to assume that something went south when you imported the data from the queries:

here is the working file:
Mastering DAX Calculations.pbix (834.9 KB)

[Greg]

I’ve never seen it happen before either …

[pete962]

Thanks @AntrikshSharma for your investigations and solution