Dax for Top N and Other

[Anonymous]

I have tried many options posted by other usres but nothing seems to work for me.

I need to create a table summary with Topn and one addition row with Others

Sample Data:

Dept.	Members	column 3	column 4
Dept1	a	…	…
Dept1	a	…	…
Dept1	b	…	…
Dept1	c	…	…
Dept2	d	…	…
Dept2	d	…	…
Dept3	e	…	…
Dept3	f	…	…
Dept4	g	…	…
Dept4	g	…	…
Dept4	h	…	…
Dept4	i	…	…
Dept4	j	…	…
Dept5	k	…	…
Dept5	k	…	…
Dept4	j	…	…

Result needed:

Top n depts (for example lets say 3):

Dept.	Distinct Memebrs Count
Dept4	4
Dept1	3
Dept3	2
Others (2)	2

Kindly suggest how I can create this summary in Power BI.  Thank you so much for all the community super users / Datanuts for helping us!

[judspud]

Hi @Anonymous 

I have achieved this before by using 2 columns. First you need to use the rank function to assign a rank to each row. You can specify how to deal with ties in the rank function. 

The next column you need is to say if the rank is less than or equal to 'n' then it is the rank value else it is "Others".

If you want to displays Others (x) then you can concatenate a countrows of the table where the rank value is above 'n'.

Hope this helps

Thanks,

George

[Anonymous]

Thank you @judspud . I can create Rank as measure but not column.  The actaul data has duplicate values and I cant remove them and if I calculate Rank as measure then I cannot use if else for n and others.  hope you understand my challenge.

[Zubair_Muhammad]

@Anonymous 

Do you need a calculated table?

Try this

Calculated Table =
VAR AllDept =
    SUMMARIZE (
        Table1,
        [Dept.],
        "Distinct Members Count", DISTINCTCOUNT ( Table1[Members] )
    )
VAR TOP3 =
    TOPN ( 3, AllDept, [Distinct Members Count], DESC )
VAR Others =
    SUMMARIZE (
        EXCEPT ( AllDept, TOP3 ),
        "Dept.", "Others",
        "Distinct Members Count", SUMX (
            VALUES ( Table1[Dept.] ),
            CALCULATE ( DISTINCTCOUNT ( Table1[Members] ) )
        )
    )
RETURN
    UNION ( TOP3, Others )

[Zubair_Muhammad]

@Anonymous 

Small modification if you want the number of departments with Others

Calculated Table =
VAR AllDept =
    SUMMARIZE (
        Table1,
        [Dept.],
        "Distinct Members Count", DISTINCTCOUNT ( Table1[Members] )
    )
VAR TOP3 =
    TOPN ( 3, AllDept, [Distinct Members Count], DESC )
VAR Others =
    SUMMARIZE (
        EXCEPT ( AllDept, TOP3 ),
        "Dept.", "Others (" & COUNTROWS ( VALUES ( Table1[Dept.] ) ) & ")",
        "Distinct Members Count", SUMX (
            VALUES ( Table1[Dept.] ),
            CALCULATE ( DISTINCTCOUNT ( Table1[Members] ) )
        )
    )
RETURN
    UNION ( TOP3, Others )

[Anonymous]

Thank you so much @Zubair_Muhammad .  It is working.  Just last favor: Can you please help me sort the results.  When I sort in the table by distinct count, the "Other" group is listed on top because it has highest count.  How can I sort the results to show topn first and then "Other" group in the last.

Thank you again!

[Zubair_Muhammad · Accepted solution]

@Anonymous 

We can add a RANK column in the calculated table, then sort by it.

Try this

Calculated Table =
VAR AllDept =
    SUMMARIZE (
        Table1,
        [Dept.],
        "Distinct Members Count", DISTINCTCOUNT ( Table1[Members] )
    )
VAR TOP3 =
    TOPN ( 3, AllDept, [Distinct Members Count], DESC )
VAR Others =
    SUMMARIZE (
        EXCEPT ( AllDept, TOP3 ),
        "Dept.", "Others (" & COUNTROWS ( VALUES ( Table1[Dept.] ) ) & ")",
        "Distinct Members Count", SUMX (
            VALUES ( Table1[Dept.] ),
            CALCULATE ( DISTINCTCOUNT ( Table1[Members] ) )
        )
    )
RETURN
    ADDCOLUMNS (
        UNION ( TOP3, Others ),
        "RANK", IF (
            CONTAINSSTRING ( [Dept.], "Others" ),
            0,
            RANKX ( UNION ( TOP3, Others ), [Distinct Members Count],, ASC, DENSE )
        )
    )

 

[Anonymous]

@Zubair_Muhammad  Thanks a million! 🙂

[Anonymous]

@Zubair_Muhammad one more question: this calculated table is not filtered with slicers.  I am using slicers for the fields that is not part of this calculated table but the original table i.e., column 3 in the slicer.

Any recommndation?