Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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. 

 

 

 

 

1 ACCEPTED SOLUTION
CheenuSing
Community Champion
Community Champion

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

 

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

View solution in original post

1 REPLY 1
CheenuSing
Community Champion
Community Champion

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

 

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors