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.
Hi, this bar chart looks kind of silly but I want to show the variety of different customer care categories per day during a week. I don't mind all the cateogies that are small, I would like an easy way to see the name of the largest categories. A grouped bar chart I think is even worse. Is it possible to have something like top 5 categories and the rest as one group to see correct total? Any suggestions?
JG
Solved! Go to Solution.
The problem with the solution from Curbal is it does not allow any segmentation of the data for a dynamic TopN. It only ranks them based on the total in the data table.
You can create a category table that also contains an 'Other' entry that you join into your main data table.
Categories =
UNION (
DISTINCT ( 'Table'[Category] ),
ROW ( "Category", "Other" )
)
Then you can write a measure like this to calculate the top 5 and group the rest in the 'Other' row.
Top 5 + Other
VAR Top_N =
CALCULATETABLE ( Categories, TOPN ( 5, ALL ( 'Categories' ), [Amount] ) )
RETURN
IF (
NOT ISFILTERED ( 'Categories'[Category] ), CALCULATE ( [Amount], ALL ( 'Categories' ) ),
IF ( SELECTEDVALUE ( 'Categories'[Category] ) = "Other",
CALCULATE ( [Amount], EXCEPT ( ALL ( 'Categories' ), Top_N ) ),
CALCULATE ( [Amount], INTERSECT ( 'Categories', Top_N ) )
)
)
[Amount] is simply the measure you are showing in your chart.
Then you replace the Category in your whart with the Category from the new Categories table you created (the one with the 'Other' row in it) to your chart.
Hi @JGG ,
Is your issue solved now?
If not,check the blog below:
https://www.sqlbi.com/articles/showing-the-top-5-products-and-others-row/
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
@JGG , refer if this approach can help
https://www.youtube.com/watch?v=UAnylK9bm1I
The problem with the solution from Curbal is it does not allow any segmentation of the data for a dynamic TopN. It only ranks them based on the total in the data table.
You can create a category table that also contains an 'Other' entry that you join into your main data table.
Categories =
UNION (
DISTINCT ( 'Table'[Category] ),
ROW ( "Category", "Other" )
)
Then you can write a measure like this to calculate the top 5 and group the rest in the 'Other' row.
Top 5 + Other
VAR Top_N =
CALCULATETABLE ( Categories, TOPN ( 5, ALL ( 'Categories' ), [Amount] ) )
RETURN
IF (
NOT ISFILTERED ( 'Categories'[Category] ), CALCULATE ( [Amount], ALL ( 'Categories' ) ),
IF ( SELECTEDVALUE ( 'Categories'[Category] ) = "Other",
CALCULATE ( [Amount], EXCEPT ( ALL ( 'Categories' ), Top_N ) ),
CALCULATE ( [Amount], INTERSECT ( 'Categories', Top_N ) )
)
)
[Amount] is simply the measure you are showing in your chart.
Then you replace the Category in your whart with the Category from the new Categories table you created (the one with the 'Other' row in it) to your chart.
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 |
---|---|
107 | |
93 | |
77 | |
65 | |
53 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |