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

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