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

Top 10 or Bottom 10 by summing up values based on another column

I am trying to do top 10 and bottom 10 by using the filter option on a data set. Below is an example. I do not have a column equivalent to sum total in the data set. Now when i do the top 10 it picks up value on the basis on values in "Amount" column. So the results are 1st is F with 100 , 2nd is B with 90 3rd is A with 80. However what is want is to do the top 10 on the basis of sum total based on "Name" column. The ideal result shall be 1st F-160 2nd E -120 , 3rd B -110 and so on. 

 

NameAmount Sum total
A10 90
B20 110
C30 94
D40 85
E50 120
F60 160
A80  
B90  
C64  
D45  
E70  
F100  

 

Can anyone suggest ways to do this?

1 ACCEPTED SOLUTION

Accepted Solutions

Hello @Sandeep_rai ,

Basically, the bar chart will show the added value automatically, you cannot play it in my environment based on the sample data. When I use top 10 or lower 10 on the bar chart, it works well and shows the added value

top10.png

Maybe you may consider sharing a sample .pbix file and some screenshots for further discussion.

Best Looks,
Yingjie Li

If this post helps, please consider Accepting it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
Super User IV
Super User IV

@Sandeep_rai , Not very clear

 

sum Total = sum(Table[Amount])

 

Rank1 = RANKX(all(Table[Name]),[sum Total],,desc,dense)

Rank2 = RANKX(all(Table[Name]),[sum Total],,asc,dense)

 

CALCULATE(if([Rank1]<=10 || [Rank2]<=10 ,[sum Total ],BLANK()) ,VALUES(Table[Name]))



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

@amitchandak -Sorry for not being clear. I am using a Top N filter function in one of my bar graph and the filter is to show top 10 and bottom 10. The issue is graph is showing top 10 by individual values and not summing it up by the Name to determine top 10 or bottom 10 at aggregated level not at a invidual line item level. Is there anyway i change my filter to show top 10 on the basis of values in "Name" column rather than values in "Amount" column

Hello @Sandeep_rai ,

Basically, the bar chart will show the added value automatically, you cannot play it in my environment based on the sample data. When I use top 10 or lower 10 on the bar chart, it works well and shows the added value

top10.png

Maybe you may consider sharing a sample .pbix file and some screenshots for further discussion.

Best Looks,
Yingjie Li

If this post helps, please consider Accepting it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
November Update

Check it Out!

Click here to read more about the November 2020 Updates!

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform October Community Highlights

Check out the top community contributors across all of the communities

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors