Power BI TopN and All Other

Question

Hi, i want to categorize the data into top n data and others. how to do it? can anyone help me.

Year    Location    Type    Amount
2015    West        Apple   12
2015    West        Pear    14
2015    East        Apple   55
2015    South       Orange  62
2015    West        Orange  64
2015    East        Banana  12
2015    North       Banana  23
2015    East        Peach   43
2015    East        Apple   89
2015    West        Banana  77
2015    West        Orange  43
2015    North       Apple   2

Here i want to categorize data into top n depending on amount for each type.

Answer 1

Hi ,

• Create a measure to calculate rank using below code.

           Rank = RANKX(ALLSELECTED(Sheet1[Type]),CALCULATE(SUM(Sheet1[Amount])),,DESC,Dense)

          ( Here the selecting all data for type in sheet1 and then calculating sum and displaying the rank in descending order. )

• Then create another measure to group the top n as "Top N" others into "Others" group.

            group = IF([Rank]<=Parameter[Parameter Value],"Top N","OTHERS")

• Add a parameter for selecting n value.​

           

• Then click ok. Now drag the pointer on the slicer for selecting n value.
• Then add the column for displaying top n and others as a column.

          

     Hope this helps you.

Comments

Cherukuri

Your solution looks quite good for me, however; is it possible to show the Type for Top N and Others for others
for example :  

Aple 158

Orange 169

Others 169

Thank you

---

Thank you, I did try following the link you sent to me .
It looked 90% fine for me , except that I'd like to show the table

Aple 158
Orange 169
Others 169

=========

Total  496

Now it shows the total of 327 (Because of the filter of Rank >0). Do you have any idea if it can really show the Net Total of TopN+Others.  I was a tableau user,  it is simply just building "Set" of Top N . While I am looking for this feature in power bi and does not find one.

Thank you