cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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

 

 

 

View solution in original post

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

 

 

 

View solution in original post

Highlighted
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?

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 219 members 2,296 guests
Please welcome our newest community members: