cancel
Showing results for
Did you mean:

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.

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

Best Regards,
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.

Best Regards,
Dallas.

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors