[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:
Answers
-
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!
0 -
@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... 🙂 )
1 -
Can't believe I missed that. Thank you!!
0 -
forgot this article completely.
This method been around "officially" since 2015 at least, I don't claim to have invented it 🙂
0 -
@nathan_rudman any good idea said it and re-said it does not heart 🙂
0 -
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 !
1 -
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
0
