Forum Discussion

Suman8877's avatar
Suman8877
Advocate II
11 months ago
Solved

I need TOPN customer ?

Hi, 

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. 

 

 

Need Help
  • rohit1991's avatar
    rohit1991
    11 months ago

    Hi Suman8877 

    The RANKX function works based on the field you are grouping by.

    For example:

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

     

    This calculation ranks values at the Customer level.

     

    • If you want to rank by a combination key (such as Region + Country + RU), you need to replace Customer with that combination key.
    • If your report has separate columns for Region, Country, and RU, this approach can produce incorrect results.
    • The key principle is: the dimension you use in your visual should be the same dimension you use inside RANKX for accurate ranking.

1 Reply

  • rohit1991's avatar
    rohit1991
    Super User
    11 months ago

    Hi Suman8877 

    The RANKX function works based on the field you are grouping by.

    For example:

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

     

    This calculation ranks values at the Customer level.

     

    • If you want to rank by a combination key (such as Region + Country + RU), you need to replace Customer with that combination key.
    • If your report has separate columns for Region, Country, and RU, this approach can produce incorrect results.
    • The key principle is: the dimension you use in your visual should be the same dimension you use inside RANKX for accurate ranking.