Power BI: TopN and All Other

Question

I have a data set that resembles the following:

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

And I need it to be summarized to show TopN as well as all other in order to keep the grand total the same. Just filtering to show only the TopN reduces the grand total and will not work...

The end result should look like this (n=3 in this example):

Type         Amount
Orange       169
Apple        158
Banana       112
All Other    57
Grand Total  496

I've gotten as far as creating a new measure for total amount:

Total_Amount = Sum(data[Amount])

But I don't know whether to proceed with RankX or TopN and I haven't found a straightforward way in Power BI to not only show the TopN, but also group everything else that would fall into the All Other category.

alejandro zuleta alejandro zuleta · Accepted Answer · 2016-06-29T03:29:34

This can be done by creating a rank measure, then use it to determine the first N types and the succeeding ones.

Create this measures:

Total:=SUM(Data[Amount])

Create the [Type Rank] measure using the [Total] measure:

Type Rank:=RANKX(ALL(Data[Type]);[Total])

Now use [Type Rank] measure to determine when aggregate the [Amount].

Top3:=IF ([Type Rank] <= 3;[Total];
  IF(HASONEVALUE(Data[Type]);
    IF(VALUES(Data[Type]) = "Others";
       SUMX ( FILTER ( ALL ( Data[Type] ); [Type Rank] > 3 ); [Total] )
    ) 
  ) 
)

Replace the 3 ocurrences by the number of Types you want to get. Also note Data is the name of the table in my example, you have to put the actual table name.

It is necessary to add Others row to your data to then put the aggregation of the greather than N types, so you can use something like this:

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
2015    East        Others

This is a pivot table I've created using your data in Excel:

This is the calculated rank for every value in the Types column:

This approach can be used in Power BI too.

Let me know if this helps you.

Power BI: TopN and All Other

1 Answers