Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
Solved! Go to 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 ) ) )
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
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.
@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 )
@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 )
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!
@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 ) ) )
@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?