Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Sandeep_rai
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

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

4 REPLIES 4
amitchandak
Super User
Super User

@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]))

Hi @amitchandak 

sum Total = SUM('XXX_Weekly_Model_Output_12Wks'[Sales Units])

Rank1 = RANKX(ALL('XXX_Weekly_Model_Output_12Wks'[Model]), [sum Total],,desc,dense)

CALCULATE(if([Rank1]<=10,[sum Total],BLANK()),VALUES('XXX_Weekly_Model_Output_12Wks'[Sales Units])

I get an error with your solution?

 

 

The syntax for 'Rank1' is incorrect. (DAX(SUM('XXX_Weekly_Model_Output_12Wks'[Sales Units])Rank1 = RANKX(ALL('XXX_Weekly_Model_Output_12Wks'[Model]), [sum Total],,desc,dense)CALCULATE(if([Rank1]<=10,[sum Total],BLANK()),VALUES('XXX_Weekly_Model_Output_12Wks'[Sales Units]))).

 

 

Unexpected expression Rank1

also when I attempt to use the sum Total in the last CALCULATE line it is not recognised

@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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.