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

 Name Amount Sum total A 10 90 B 20 110 C 30 94 D 40 85 E 50 120 F 60 160 A 80 B 90 C 64 D 45 E 70 F 100

Can anyone suggest ways to do this?

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support

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

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.

3 REPLIES 3
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]))

Proud to be a Super User!

Regular Visitor

@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

Community Support

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

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.

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Power Platform October Community Highlights

Check out the top community contributors across all of the communities

#### 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