Calculate RankX

[Andrew_UK]

Hi All,

Apologies if this has already been asked, I did a search but couldn't find a result on it. This is my first time using RankX or Rank.eq and I can't seem to get either to do what I need them to.

Essentially I need to produce a report showing the top 10 products within each Product Group. My table called "Analysis" has already brought in Product Code, Product Group and the total sales quantity over the last year.

So I thought this would work (based on previous successful uses of the calculate function:

=calculate(rankx(Analysis,[Quantity],[Quantity]),Filter(Analysis,[Product Group]=Earlier([Product Group]))) ... It gave an error

So I broke it down to try to problem solve it. And the following works as ranking ALL sales quantities across all groups:

=Rankx(Analysis,[Quantity],[Quantity])

Similarly I tried the following formula as a test of my Calculate function and it successfully gave me the total sales for each group

=calculate(sum([Quantity]),Filter(Analysis,[Product Group]=Earlier([Product Group])))

So how do I get the additional criteria to work for me? I just want it to tell me that The top selling Soft Drinks are 1, 2, ... 10 and the top selling Hot drinks are 1, 2, ... 10 etc etc

I realise I'm probably missing something very obvious. Many thanks in advance for your help!!

Andrew

 

[Matt Allington]

You don't really specify how your data model is set up, so I will assume you are using 1 table. Note this is not optimal (read about that here)

So my guess is you are trying to display the top 20 products in a pivot table, with the numbers 1 - 20 in the values section. Is that correct? Assuming this is what you are wanting to do, your problem is that the pivot table creates a filter context from the rows of your pivot table before RANKX is calculated. This has the effect of ranking each product against only 1 product - itself. To solve this, you need to remove the initial filter context before doing the rankx as follows:

=Rankx(ALL(Analysis),[Quantity])

I cover the concept of Filter Context and how to read the context from a pivot table in my book Learn to Write DAX

 

[Andrew_UK]

Hi Matt,

Thanks for your reply, apologies for not getting back sooner. You are correct that it was just one table I was looking to build this in.

In the end I found that you can just use Filter(Analysis,[Product Group]=Earlier([Product Group]) in place of Analysis

Then it's just Rankx(Filter(Analysis,[Product Group]=Earlier([Product Group]),[Quantity],[Quantity])