Reply
Highlighted
Frequent Visitor
Posts: 3
Registered: ‎12-18-2016
Accepted Solution

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

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. 

 

 

 

 

Mannu

Accepted Solutions
Senior Member
Posts: 352
Registered: ‎02-17-2016

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

Hi @mannu

 

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

 

View solution in original post


All Replies
Senior Member
Posts: 352
Registered: ‎02-17-2016

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

Hi @mannu

 

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