Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
26 | |
20 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
43 | |
19 | |
18 |