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.
Hello All!
I'd like to know if there is a way how to build a line chart with the Top N Filter + Other + Specifc item. I was asked to add one item that doesn't show in top N because it is too low, but we need to show it anyway.
Ex:
Table food
Potato - 500
Rice - 300
Bean - 400
Onion - 250
Carrot - 50
Meat - 800
Now I'll show what i want:
Top 3 table + other + Carrot:
Meat - 800
Potato - 500
Bean - 400
Other - 550 (rice + onion)
Carrot - 50
So the ideia is to force this item (Carrot) to enter in top 3 even if it is high or low..
Thank you!!
Solved! Go to Solution.
hi,@Lupiyo
After my research, I did a demo with reference to this article:https://community.powerbi.com/t5/Desktop/Show-Top-N-and-sum-up-the-Others-as-a-value/m-p/313063#M138...
Basic table
Result:
in this demo, One specific item needs to be the specific rank of all categories.
Here is demo , please try it.
https://www.dropbox.com/s/aav3f2g16zb92t6/Top%20N%20and%20Others%20-%20monthly.pbix?dl=0
Best Regards,
Lin
hi,@Lupiyo
After my research, I did a demo with reference to this article:https://community.powerbi.com/t5/Desktop/Show-Top-N-and-sum-up-the-Others-as-a-value/m-p/313063#M138...
Basic table
Result:
in this demo, One specific item needs to be the specific rank of all categories.
Here is demo , please try it.
https://www.dropbox.com/s/aav3f2g16zb92t6/Top%20N%20and%20Others%20-%20monthly.pbix?dl=0
Best Regards,
Lin
Hey,
I guess you can adopt this solution to your needs:
https://www.minceddata.info/2018/06/06/topsomething-and-all-the-rest-called-other/
Regards,
Tom
I was studing this code, i'm not familiar with the sintax but I could note a couple of things and have doubts:
top 5 and other = var top5 = CALCULATETABLE(TOPN(5,VALUES(Table1[Month]),CALCULATE(SUM('Table1'[Amount])))) var other = ROW("Month", "Other") var allTheRest = CALCULATE(SUM(Table1[Amount]), EXCEPT(VALUES('Table1'[Month]),top5)) var theUnion = UNION(top5,other) return SUMX( INTERSECT('Unrelated',theUnion) , var currentIterator = 'Unrelated'[Month] return IF( 'Unrelated'[Month] <> "Other" ,CALCULATE( SUM('Table1'[Amount]) ,'Table1'[Month] = currentIterator ) ,allTheRest ) )
When i consider all the rest, i'm excluding top5, but what about the "other"? all te rest should be the others, in my case, and the other are the carrot?
Hey,
can you please provide a PBIX file with sample data, upload the file to onedrive and dropbox, and share the link.
Regards,
Tom
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 |
---|---|
105 | |
94 | |
75 | |
63 | |
62 |
User | Count |
---|---|
137 | |
105 | |
104 | |
80 | |
63 |