Calculating Percent of Subtotal

The new decomposition tree visual adds a whole other level to how we can show data based on hierarchical information. In this Power BI tutorial, I’m going to show you how you can show the % of subtotal from the previous category. This is probably explained best after watching the following gif:

You can see how the % total resets for every level of the tree. Out of the box, the decomposition tree visual (and other visuals including the Matrix) only allows us to view the % of grand total which isn’t nearly as cool. I would rather see the percentage of the values in my current selection compared to the total of the current node. This is easily achieved with the following measure.

Revenue % =

VAR CurrentRetailerType = VALUES(Sales[Retailer type])

VAR CurrentProductLine = VALUES(Sales[Product line])

VAR RevenueForCurrentSelection = SUM(Sales[Revenue])

VAR TotalRevenue =

SWITCH(

    TRUE(),

    ISINSCOPE(Sales[Retailer country]),CALCULATE(SUM(Sales[Revenue]),ALLSELECTED(Sales),Sales[Retailer type] IN CurrentRetailerType,Sales[Product line] IN CurrentProductLine),

    ISINSCOPE(Sales[Product line]),CALCULATE(SUM(Sales[Revenue]),ALLSELECTED(Sales),Sales[Retailer type] IN CurrentRetailerType),

    ISINSCOPE(Sales[Retailer type]),CALCULATE(SUM(Sales[Revenue]),ALLSELECTED(Sales))

)

RETURN

IF(

    ISINSCOPE(Sales[Retailer type]),

    DIVIDE(RevenueForCurrentSelection,TotalRevenue),

    1

)

It may look like a lot, but I assure you that it’s easy once you wrap your head around it. In the first two lines, we are simply grabbing the VALUES of each of our levels of the hierarchy (not including the deepest level). Next, we calculate the “RevenueForCurrentSelection” variable which is simply the SUM of my Revenue field. This takes all of the filters into account by default.

The next section is the most important to understand. I am creating a variable named “TotalRevenue” that is comprised of a SWITCH statement in order to understand the current level of the hierarchy for the calculation. Using the ISINSCOPE function, we can determine exactly which level to apply the calculation for. For example, if my hierarchy is made up of Retailer Type, Product Line, and Retailer Country in that order and I know that my Product Line column is in scope in the visual, then I need to calculate the measure a certain way. Starting from the deepest level of the hierarchy, I check if Retailer Country ISINSCOPE. If it is, I want to calculate the SUM of Revenue removing all of the filters on the table with ALLSELECTED(Sales) and then applying my individual filters for Retailer Type and Product Line.

Let’s say that Retailer Country wasn’t in scope. That would mean that we are at the first or second level of our hierarchy. The other two clauses in the SWITCH statement check the respective levels of the hierarchy and reapply the necessary filters.

Finally, I added a clause in the RETURN statement to check if the first level of the hierarchy is in scope. If not, return 1 (100%). If yes, then return RevenueForCurrentSelection divided by Total Revenue. This returns the correct % of the previous subcategory.

Make sure to check out the video up above for an even deeper explanation of this method. If you enjoyed this trick, make sure to subscribe to the BI Elite YouTube channel and the BI Elite blog!