TopN Customers by Revenue filtered by Region, Country and, Reporting Unit

Anonymous — Tue, 10 Jan 2017 15:24:35 GMT

Hi,

I m new to DAX and Power BI and not able to make out reason for inconsistent result.

There are slicers for Region, Country and, Reporting Unit in Power BI report and I need to show top 5 customers from TodaysTable data based on the selection made.

Table structure:
HistoryTable - with Region, Country, Reporting Unit, Revenue, & CombinationKey (REG + COUNTRY + RU)
TodaysTable - with Region, Country, Reporting Unit, Customer, Customer ID, Revenue & CombinationKey (REG + COUNTRY + RU)
MapTable - Active Relationship, Both directions on CombinationKey - DISTINCT(ALL(HistoryTable [CombinationKey], HistoryTable [Region], HistoryTable [RU], HistoryTable [Country]))

I created 2 Measures in TodaysTable

Todays Revenue = SUMX(TodaysTable , TodaysTable [Net Order Value])
Rank of Customer = RANKX(ALLSELECTED(TodaysTable [Customer]),[Todays Revenue],,DESC,DENSE)

and applied filter Rank of Customer <=5 in the report.

While this shows the result correctly when I have only Customer and Todays Revenue as columns in the table.

However, if I include Customer ID/ Region/ Country number of results shown just multiplies and I noticed that the Rank is repeating (multiple rows shows up as having Rank 1 while having different Revenues !!!) .

I m unable to understand this behavior.

It would be nice if someone could help me with correct DAX expression. that would enable me to show only 5 customers based on max revenue with additional fields (Region, Country, Customer ID).

Also, I would like to know if this can be achieved with a single DAX expression.

Thanks in advance.

Re: TopN Customers by Revenue filtered by Region, Country and, Reporting Unit

Anonymous — Mon, 23 Jan 2017 04:59:15 GMT

Hi Anonymous

RANKXX function works on what you are grouping by

When you say

Rank of Customer = RANKX(ALLSELECTED(TodaysTable [Customer]),[Todays Revenue],,DESC,DENSE)

It works based on the Customer. So if you want with CombinationKey (REG + COUNTRY + RU) you have to replace it Customer with this CombinationKey.

Caution this will also report wrong results if your report has separate columns for Region, Country and RU.

The basics is that the any report based on RANKXX should use the same dimension on which the rank is computed.

If this answers your issue please accept it as a solution and also give KUDOS.

Cheers

CheenuSing

topic TopN Customers by Revenue filtered by Region, Country and, Reporting Unit in DAX Commands and Tips

TopN Customers by Revenue filtered by Region, Country and, Reporting Unit

Re: TopN Customers by Revenue filtered by Region, Country and, Reporting Unit