Top Level Item Filter to Show Subtotal
Hi All,
I have a module with a composite hierarchy below. I would like to filter with a boolean line item for all countries in the Americas and get their total 50 show in the ALL Regions (Top Level Item).
Also when the filter is applied I want the ALL Regions (Top Level Item) to show in the list items.
How can I achieve this?
Filter Module
When filter is applied I get the total for all items in the hierarchy
Instead I want this total to show
Please any idea or suggestion will be helpful.
Best Answer
-
I got a workaround to only show selected sums at the top levels within the same input line item. Consider this example: employees roll up to the department, and in the employee module, we are storing active employee Boolean. The requirement is to only sum up the data for active employees at department and total department levels.
1) Create a line item called Active Amount and write the formula. Set the summary to SUM.
Active Amount: IF Active? THEN Amount ELSE 0
2) Create a line item called Dept Totals, refer Active Amount line item, set the summary to SUM, and apply Department list as dimension within same module.
3) Create a line item called Denominator, formula as 1 and remove the dimension. Set summary of Amount line item as Ratio with Dept Totals as numerator and Denominator line item as denominator.
Sharing the screenshots:
Hope this helps.
0
Answers
-
Hi,
Top level always gives a sum total of every item available. So even after applying filter, still it would give a total of all values including hidden ones. You cannot use selective access either to limit the total. Here also the result would be the same
I am not sure of your exact requirement. However, if you still want to have a total for specific region at top level, there is a workaround
Use list subsets of countries for each region as required.
For example in your case, create Americas Only subset of G2 Country list
Now using this subset, create a staging module similar to the original module, and refer to original item
End result:
Cheers
Sai
0 -
Hi @Saiteja66 , thanks for the response. I was looking to see if there would be a work around with just using a boolean filter and without using a list subset. I should have mentioned that earlier.
0
