Forum Discussion

clim2f88j's avatar
clim2f88j
Frequent Visitor
2 years ago

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 t...
Need Help
DallasBaba's avatar
DallasBaba
Skilled Sharer
2 years ago

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