Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
I've watched and read a bunch of different ways to do this and still can't figure it out.
I have a list of departments that one of them is picked when working a complaint. Therefore, to get the total count for each department that was picked, I do a DISTINCTCOUNT(Department). I can create a pie chart that will show the percentage of complaints by department, by using the department in the value as well and selecting it as count.
All the videos i watched and post i read, are using a "sales" value which doesn't excist in my data, and i haven't been able to find a way around this.
I want to be able to do a top N, lets say top 3, and then in the graph show "Others" which is the total of the remaining departments.
Department
A
B
C
D
E
F
@clim2f88j One possible solution to this problem is to ensure that the two tables in the UNION function have the same number of columns. In this case, the first table has two columns (Department and Count), while the second has only one column (OtherDept).
To fix this, we can add a second column to the second table with a value of 0, like this:
Top N =
VAR TopN = 3
VAR OtherDept = "Others"
VAR TopNDept = TOPN(TopN, VALUES('Table'[Department]), CALCULATE(DISTINCTCOUNT('Table'[Department])))
VAR OtherDeptCount = CALCULATE(DISTINCTCOUNT('Table'[Department]), NOT('Table'[Department] IN TopNDept))
VAR TopNDeptCount = CALCULATE(DISTINCTCOUNT('Table'[Department]), 'Table'[Department] IN TopNDept)
RETURN
UNION(
ADDCOLUMNS(
VALUES('Table'[Department]),
"Count", TopNDeptCount
),
ROW(OtherDept, OtherDeptCount),
ROW("Dummy", 0)
)
Or can you send a pbix file with sample data So I can debug for a solution?
If my answers help arrive at a solution? Give it a kudos by clicking the Thumbs UP
@Syndicate_Admin @ you can use the “Top N” filter in Power BI to create a top N chart.
You can select the department column as the field and set the filter to show the top 3 departments.
To show the remaining departments as “Others”, you can create a new column in your data that groups all the departments that are not in the top 3 as “Others”.
You can then use this column as the value in your pie chart.
Here is the DAX code to use :
Top N =
VAR TopN = 3
VAR OtherDept = "Others"
VAR TopNDept = TOPN(TopN, VALUES('Table'[Department]), CALCULATE(DISTINCTCOUNT('Table'[Department])))
VAR OtherDeptCount = CALCULATE(DISTINCTCOUNT('Table'[Department]), NOT('Table'[Department] IN TopNDept))
VAR TopNDeptCount = CALCULATE(DISTINCTCOUNT('Table'[Department]), 'Table'[Department] IN TopNDept)
RETURN
UNION(
ADDCOLUMNS(
VALUES('Table'[Department]),
"Count", TopNDeptCount
),
ROW(OtherDept, OtherDeptCount)
)
Thank you for your help, but that didn't work. I'm getting a "The syntax for 'TopN' is incorrect" error.
I added '__' after each VAR and that fixed the error i was getting before, but now i'm getting a "each table argument of 'UNION' must have the same number of columns.
@clim2f88j you can use the “Top N” filter in Power BI to create a top N chart.
You can select the department column as the field and set the filter to show the top 3 departments.
To show the remaining departments as “Others”, you can create a new column in your data that groups all the departments that are not in the top 3 as “Others”.
You can then use this column as the value in your pie chart.
Here is the DAX code to use :
Top N =
VAR TopN = 3
VAR OtherDept = "Others"
VAR TopNDept = TOPN(TopN, VALUES('Table'[Department]), CALCULATE(DISTINCTCOUNT('Table'[Department])))
VAR OtherDeptCount = CALCULATE(DISTINCTCOUNT('Table'[Department]), NOT('Table'[Department] IN TopNDept))
VAR TopNDeptCount = CALCULATE(DISTINCTCOUNT('Table'[Department]), 'Table'[Department] IN TopNDept)
RETURN
UNION(
ADDCOLUMNS(
VALUES('Table'[Department]),
"Count", TopNDeptCount
),
ROW(OtherDept, OtherDeptCount)
)