Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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
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.
@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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |