How to sum / aggregate on the right parent child hierarchy level using a hierarchy slicer?

[janstrauss1]

Dear Power BI Community,

I’m trying to build a report with a hierarchy slicer that allows to filter and aggregate values based on different levels of a ragged hierarchy (a classification of economic activities) with varying depths.

The challenge is that in my dataset, totals in the hierarchy have been pre-calculated, so a simple sum leads to double counting and would be incorrect as you can see from the .pbix file of my reproducible example (ReprEx) that you can download from my Github.

When a user selects, for instance, ‘301 Building of ships and boats’ on the hierarchy slicer, the shown sum of 39668 is wrong and should be 19834 for the selected example year of 2010.

Overall, my scenario is similar to the one described in Kasper’s classic post on how to Use IsInScope to get the right hierarchy level in DAX and I’ve read a few other great posts (incl. https://www.daxpatterns.com/parent-child-hierarchies/, https://www.sqlbi.com/articles/clever-hierarchy-handling-in-dax/) but did not find any satisfying solution for my issue.

Inspired by the solution suggested by @v-deddai1-msft Matrix hierarchy sum issue, I’ve managed to create a measure that does the job when using a matrix visual.

However, instead of a matrix, I want to visualize the calculated (measure) values in a column or line charts!
Yet, column and line chart visuals do not work with measures in this context.

So, for the given example, the bar height should be 19834 for the year 2010 when selecting the ‘301 Building of ships and floating structures’ hierarchy on the slicer.

I appreciate any help and suggestions.

Many thanks in advance!

P.S.: Note that I currently can achieve my desired result using CTRL + Select for multiple selection of subcategories via the hierarchy slicer but this is not intuitive and user friendly:

[janstrauss1]

Thanks a lot for your feedback @AlexisOlson !

It's very helpful to know that "there's nothing particularly special about the matrix visual" and that the same measure on the bar chart should match the grand total row in the matrix.

I was actually expecting that but think got confused and started to get doubts, because I only blanks showed up when using the measure in a bar chart (see screenshot) based on your feedback it now makes sense.
Updated example .pbix downloadable from my Github.

https://github.com/janstrauss1/PowerBI_data/blob/main/PowerBI_ReprEx_HierarchySlicer/ReprEx_HierarchySlicerSumIssue_v3.pbix

So, now I at least know that I need to somehow fix my measure that it calculates a correct grand total.
Not really sure yet why it currently doesn't do that...

---

[janstrauss1]

Thanks for feedback @AlexisOlson !

Yes, I also concluded that it's probably the cleanest approach to filter out any rollup rows from the fact table when they are additive. Thus, I filtered my fact table for values of leafs of my hierarchy and then do simple sums to visualise the data as column bar chart.

However, looking at the data, I then realized that there are a few cases were this doesn't work, because the data is unfortunately not completely additive in all cases and some of the lowest hierarchy level data is not available.

You can see this in my updated .pbix reproducible example that can be downloaded from my Github

While this almost behaves like I want to, the problem persists that I'm unable to visualize the measure value in a column bar chart resulting in a discrepancy between the bar chart and the matrix (e. g. see screenshot below for year 2010).

Thus, since I have non-additive metrics and aggregate values at every level, I think I might need some approach similar to what you describe atHandling Subtotals for Pre-Calculated (Non-Additive) Measures

Have you tried to visualize the results of such approach using other visuals than the matrix visual (e.g. line or bar charts)?

[AlexisOlson]

You can't use only the lowest level then but you might still be able to use the lowest available level and still have it roll up as expected.

There's nothing particularly special about the matrix visual. If you use the same measure on the bar chart, the value should match the grand total row in the matrix (looks like that's blank for the measure you're using in the matrix though). If your matrix total works, then your bar chart should also work (assuming you aren't using a stacked bar chart).

[janstrauss1]

Dear Power BI community,

Thinking a lot about my issue, I figured that it’s maybe the cleanest and easiest approach to filter for the lowest level children of my ragged parent child hierarchy, so I’d be left with only the leaves.

Using that approach, I’d be able to calculate simple sums from my data values avoiding the problem of pre-calculated sums on higher hierarchy levels in my dataset.

Thus, I did some searches and reading around my question on how to retrieve the lowest level hierarchy members using Power Query or DAX?

Indeed, I found some very interesting posts including one from @Meagan on Retrieving Lowest Level Hierarchy Members and Leaves in MDX that frames my issue very well.

Additionally, I read interesting posts from @ImkeF: Creating a custom column in Power BI for lowest level in a hierarchy, and atFast Power Query Code for getting the lowest level children in Parent/Child Hierarchy

Unfortunately, I didn’t manage to implement this to solve my problem and would be grateful for any help and suggestion! @marcorusso @AlbertoFerrari @AlexisOlson @amitchandak

Many thanks in advance!

[AlexisOlson]

I've written on a similar topic.
https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Handling-Subtotals-for-Pre-Calculate...

Maybe this will help?

[janstrauss1]

Hi @AlexisOlson,

many thanks for your response! That's a very interesting post and I've bookmarked it.

Yet, unfortunately it doesn't help with my current problem that boils down to not being able to use a measure in a column or line chart visual as is possible for a matrix visual.

[AlexisOlson]

I've given this an attempt but I think I've concluded that it's probably possible to write a measure that behaves like you want, it will be fairly convoluted. I believe a better approach is to change what rows you load in your fact table.

If the metric(s) you care about are additive, then filter out any rollup rows in your fact table so that you can do a simple sum of data at the lowest level you have available.

If you're dealing with non-additive metric(s) where the previous suggestion doesn't work, then make sure your fact table includes aggregate values at every level you want to display, not just some of them. Then you can use an approach similar to the post I mentioned above.

[janstrauss1]

Hi @v-xiandat-msft,

You can download my reproducible example as a .pbix file on my Github

Sorry, I think this didn't become clear from my initital post!

[v-xiandat-msft]

Hi @janstrauss1 ,

Can you share the pbix file, thank you!

Best Regards,

Xianda Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.