cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Top N and Others

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

5 REPLIES 5
Super User

@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(
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

Thanks
Dallas

@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(
VALUES('Table'[Department]),
"Count", TopNDeptCount
),
ROW(OtherDept, OtherDeptCount)
)``````

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was helpful in other ways, please consider giving it a Thumbs Up.

Best Regards,
Dallas.

Frequent Visitor

Thank you for your help, but that didn't work. I'm getting a "The syntax for 'TopN' is incorrect" error.

Frequent Visitor

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.

Super User

@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(
VALUES('Table'[Department]),
"Count", TopNDeptCount
),
ROW(OtherDept, OtherDeptCount)
)``````

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was helpful in other ways, please consider giving it a Thumbs Up.

Best Regards,
Dallas.

Thanks
Dallas

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.