[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.



How do you achieve this ? Like this:



And here are the formulas:


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 

    Finally got to this. Amazed this isn't a best practice. We really need a good solution for the filtering. Thank you for posting this!

  • @nathan_rudman  thanks for sharing.. .

    @JaredDolich  there was some old article in some previous Community releases... I am not able to find it anymore the original solution.


    I found only this post



    I always used this solution for multi-level filtering. 


    Ratio summary method: really powerful. I also used it for example to "stop" the aggregations from some level up.. (for example when you do not want to have Local Currencies values aggregated at Total Group... 🙂 )



  • @alexpavel 

    Can't believe I missed that. Thank you!!

  • forgot this article completely.

    This method been around "officially" since 2015 at least, I don't claim to have invented it 🙂

  • @nathan_rudman  any good idea said it and re-said it does not heart 🙂

  • I remember when my boss then had the idea it was possible, he challenged me and another guy, who managed it first.

    T'was the days !

  • mkarn
    edited May 29

    Thank you for your sharing!

    I tried to replicate the solution, however, currently I am facing an error saying ' Level mismatch on common dimension' when applying the ratio as a summary for line item 'Combined'

    Ration summary : G2 Country Level/ Technical Ratio

    Attached below the screenshot for your reference