Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I want to display a pie with the top 10 brands we are selling + one slice regrouping all the other brands (11 slides total).
I successfully did that with the right measures, but it's only working for all our sales. Filtering on a specific year is not working, nor any other filter.
I can't find a way to make it work. I found several ways to achieve this top N + "others", but it's never working with filters. Since my online researches are failing, I thought that maybe someone here was more clever than me. Any way to do that ? Many thanks for any help ! 🙂
Hi, you can implement using the solution below:
Create a Table, which union all categories from source table and hard coded “Other”
TableName = CALCULATETABLE(UNION(VALUES(SourceTableName[CategoryColumn]),ROW("SourceTableName ","Other")),FILTER(SourceTableName, SourceTableName[CategoryColumn]<>""))
* Filter is needed when there’s bad data.
Create Measure – TopX &Rank
TopX=n
* n is the number of the top category that you would like to display
Rank =
IF(SELECTEDVALUE(TableName[ColumnName])="Other",[TopX]+1,RANKX(ALL(TableName[ColumnName]),[Count Client]))
Calculate the Top Category
Top = if([Rank] <= [TopX], [Count Client],
IF(SELECTEDVALUE(TableName[ColumnName])="Other",SUMX(FILTER(all(TableName),[Rank]>[TopX]),[Count Client])))
Here's an article from the masters:
https://www.sqlbi.com/articles/showing-the-top-5-products-and-others-row/
Proud to be a Super User!
User | Count |
---|---|
88 | |
74 | |
69 | |
65 | |
58 |
User | Count |
---|---|
104 | |
94 | |
76 | |
62 | |
59 |