Skip to main content
cancel
Showing results for 
Search instead 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

Reply
clim2f88j
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
DallasBaba
Super User
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(
        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

Thanks
Dallas
Syndicate_Admin
Administrator
Administrator

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

 

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.

 

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.

DallasBaba
Super User
Super User

@Syndicate_Admin 

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

 

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

Top Solution Authors
Top Kudoed Authors