Summarise the level below in a separate visualisation when drilling through the hierarchy

[Anonymous]

Hi

I've used this forum many times to answer my PBI problems but this one has stumped me!

I've been asked to show 2 visualisations (amongst others) on a page:  The first is a Waterall chart and allows you to drill up and down a hierarchy.  The second dynamically shows you the top 5 sales categories of the level below (the hierarchy tier that is 1 level below the one you have drilled into on the first chart).  I.E. If you drilldown, the second table summarises the top 5 of the associated sub hierarchy (hierarchy level -1) depending on which level you are looking at.

E.g. I click on a category at 'level 2' on my chart, the 2nd table shows me top 5 of the 'level 3' within that category.  I'm looking at a category at 'level 3' on my chart, the 2nd table shows me top 5 of the 'level 4' within that category.  Etc.

Does anyone have any ideas of I could achieve this please?

My hierarchy table is seperate to my sales table, joined with a key.  The hierarchy table has a column per level.

Amongst other things, I've had a go at creating a duplicate of my hierarchy table and unpivoting it - Then joining it into my hierarchy table with my key (with crossfilter set to both).  I was hoping I could use a combination of IF statements and ISFILTERED / ISINSCOPE to dynamically filter this to just summarise the dimensions the way I needed them.  I've not got it to work though 😞

Any help on this problem would be really appreciated!!!!

Thanks, Si

[amitchandak]

@Anonymous , refer if this can help

https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/

[Greg_Deckler]

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.