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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With