cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Mhetre123 Regular Visitor
Regular Visitor

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Jessica_Seiya Established Member
Established Member

Re: TOPN for two columns

You can follow the steps below to get the Top5 values.

 

1. Create a measure to sum Total Charges

SumTotal = SUM(Data[TotalCharges])

2018-10-19_11-26-57.png

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

2018-10-19_11-29-27.png

 

 

 

4 REPLIES 4
Super User
Super User

Re: TOPN for two columns

@Mhetre123

 

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

Mhetre123 Regular Visitor
Regular Visitor

Re: TOPN for two columns

 

Hi Zubair,

I tried this but ranking is not working for me.

 

 

Rank = RANKX(Data, Data[TotalCharges],1,ASC,Dense)

ranking.PNG

 

pbix file : https://1drv.ms/u/s!AhVscPhmToJ0k7gby4TGxHoEdq9z8Q

 

 

 

Jessica_Seiya Established Member
Established Member

Re: TOPN for two columns

You can follow the steps below to get the Top5 values.

 

1. Create a measure to sum Total Charges

SumTotal = SUM(Data[TotalCharges])

2018-10-19_11-26-57.png

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

2018-10-19_11-29-27.png

 

 

 

Mhetre123 Regular Visitor
Regular Visitor

Re: TOPN for two columns

Hi @Jessica_Seiya,

Incredibale! Thanks a lot Smiley Happy

But i have no idea how it works. Smiley Tongue Is it fairly difficult level task?

 

Are there any tutorials available to practice DAX functions?