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!
9 Comments
Leave a ReplyCancel reply
This site uses Akismet to reduce spam. Learn how your comment data is processed.
where did you get the decomposition tree visual?
Thank you very much!!!!!!!!!!!!
explaining in layman term for beginners,would have been awesome
Hello Parker,
Great tutorial! Thank you for sharing.
I applied your code to a Stacked Column Chart with a percent line. Would it be possible to add a layer such as a Column Series (Ex: Countries) and the line automatically compute the percentage relative to the higher level including all the world data?
If yes could you provide a hint regarding the tinkering to the code?
Thank you in advance,
LH
How would this work if the retailer type and product would have to come from a dimension table?
I’ve been looking for a way to do this for days! Thanks so much for explaining how to get this to work!
Can you please guide on what modification will be done if we want to calculate the average revenue per level, in absolute terms and not percentage? Thanks
What would you do if it has no hierarchy between subcategories?
Hello, what if I have 4 layers instead of 3 layers like you have in this example (retailer type, product line, retailer country), then for the last part where it says: IF(
ISINSCOPE(Sales[Retailer type]), – do I need to add anything special? Because I tried the same thing that you have except with another layer between retailer type and product line, I added “product group” and now it is showing 100% for product group when I expand retailer type. Please let me know what to do when there is 4 categories in the decomposition tree instead of 3. Thank you