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_rudmanthanks for sharing.. .
@JaredDolichthere 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...
)
forgot this article completely.
This method been around "officially" since 2015 at least, I don't claim to have invented it