Forum Discussion
TOPN for two columns
I have three columns in subscriber dataset. - Customer ID, Monthly Charges and total Charges.
I grouped Monthly charges column as below
| 0 to 20 |
| 21 to 40 |
| 41 to 60 |
| 61 to 80 |
| 81 to 100 |
| Greater than 100 |
I need to display top 5 customer IDs according to "total charges" for each group of "Monthly charges". How do I do that?
You can follow the steps below to get the Top5 values.
1. Create a measure to sum Total Charges
SumTotal = SUM(Data[TotalCharges])
2. Create a measure to get the Rank number
Rank = RANKX(ALL(Data[Customers]),[SumTotal],,DESC)
3. Create a Matrix using Monthly Charges, Customer, SumTotal and Rank than use filter feature to only show Top5
4 Replies
Anonymous
You can create a RANK column to RANK each ID within these GROUPs (using RANKX dax function)
Then you can use a VISUAL filter with RANK <=5 to get TOP5 IDs for each Group
Hi Zubair,
I tried this but ranking is not working for me.
Rank = RANKX(Data, Data[TotalCharges],1,ASC,Dense)
pbix file : https://1drv.ms/u/s!AhVscPhmToJ0k7gby4TGxHoEdq9z8Q
You can follow the steps below to get the Top5 values.
1. Create a measure to sum Total Charges
SumTotal = SUM(Data[TotalCharges])
2. Create a measure to get the Rank number
Rank = RANKX(ALL(Data[Customers]),[SumTotal],,DESC)
3. Create a Matrix using Monthly Charges, Customer, SumTotal and Rank than use filter feature to only show Top5
