[Guide] Multi Level filter selection on composite hierarchy

If you are using a multi level composite hierarchy as your main reporting list (as you should vs ragged), you have encountered the need to filter it and display many levels of it in many different dashboards.

Recently re-discovered  a solution that I find quite handy and decided to share it here.

 

Here's the result. As you can see below, a user can select any level of the hierarchy, one by one and then all is concatenated into a final line item that you can use as a filter, to create the two grids below.

I'm adding a "filter" dimension to that module so you can have several filters pre set.

nathan_rudman_0-1608210907060.png

 

How do you achieve this ? Like this:

nathan_rudman_1-1608211056398.png

 

And here are the formulas:

nathan_rudman_2-1608211107915.png

The trick is on the summary level being Ratio. This allows us to bring the value of a parent level into the summary level of a child, like this:

nathan_rudman_3-1608211180294.png

 

 

Tagged:

Answers