How to set intuitive filtering by category hierarchy?

[janstrauss1]

Dear Power BI Community,

I’m trying to build a user-friendly report with a hierarchy slicer that allows for filtering and aggregating values based on different levels of a ragged classification hierarchy.

I’ve created a fully reproducible example (ReprEx) including example data that you can find on my GitHub.

The final report should give something like the following results:

While the filtering using either the standard slicer or custom HierarchySlicer works in general, I find it not very intuitive for the user. However, I’m struggling to improve it. Specifically, when I select a category from the third level (e. g., 301 Building of ships and boats), then values for this category and all its subcategories are summed leading to double counting and wrong numbers in the line chart visual:

What I’d like to achieve is a slicer with blanks removed that shows the same filtering behaviour and results as in the first report screenshot when selecting the fourth level (Blank) but when selecting the third level category 301 Building of ships and boats.

Currently I can only achieve this by multiple selection of the subcategories using CTRL + Select like so:

I appreciate any help and suggestions how to achieve this.

Many thanks in advance!

[janstrauss1 · Accepted solution]

Dear Power BI Community,

I managed to solve my problem after I realized that I need to simply filter for the lowest level of my hierarchy to achieve correct aggregation of my values using my hierarchy slicer.

I've updated the reproducible example (ReprEx) available on my GitHub, where create a table using DAX that I use for the line chart visual:

ReprEx_FactTable_Level5filtered = 
    FILTER(
        ReprEx_FactTable, 
        LEN([WZ08_Code]) = 5
    )