I have a module where I have sales per country. I want to average sales and not add when I roll up to region or when I chose region from drop down. How can I do that?
Try changing your summary method from "Sum" to "Average"
@SnehaPrabha You can use Average Summary for that line item from blueprint of the module.
Cheers!!
@SnehaPrabha As everyone mentioned, try this out! Changing the Summary method and explore bunch of other summary methods!
Thanks SRoberts, Himansu and Dikshant I tried it but I have a level within country were I need sum. So the below example when I use average the Sparkling shows average rather than sum. It serves the purpose when I do the report as a region only the subtotals shows average, am I doing something wrong. I need the subtotal like Sparkling, Still etc to add (sum) but when I do it by region instead of Turkey it should average, subtotals should still be total. Hope I explained it clearly.
I also want to let you know that I also have quarterly summary in the same module
Sum and Time: Average work well when I chose country but when I chose region it shows all country numbers added up
@SnehaPrabha - Can you pivot and put the country dimension in rows and show where, for two countries, it shows SUM at the region level?
Hi @SnehaPrabha,
I hope my understanding of your requirement is correct:
Scenario 1: You are looking at Country & Product Parent (Sparkling, Still etc.) level
→ Summary should be SUM
Scenario 2: You are looking at Region & Product (Juice, Tea) level
→ Summary should be Average
Scenario 3: You are looking at Region & Product Parent level
The solution is a bit complicated, so if someone has a better solution please let me know :)
Step 1: Create a module to calculate Cross Level Parameters
You need to create a line item for each of the hierarchy level. Please pay attention to the summaries and applies to.
Ratio for highest level (e.g. Region)
Ratio for other levels (e.g. Country)
The idea of this module is to identify and mark the points where different summaries needed.
Combo Result: IF Country Hierarchy <> 3 AND Product Hierarchy = 5 THEN 6 ELSE IF Country Hierarchy <> 3 AND Product Hierarchy <> 5 THEN 7 ELSE Country Hierarchy
IF Country Hierarchy <> 3 AND Product Hierarchy = 5 THEN 6 ELSE IF Country Hierarchy <> 3 AND Product Hierarchy <> 5 THEN 7 ELSE Country Hierarchy
Step 2: Calculate the final result
Please pay attention to summaries and applies to.
Final Result: IF Cross Level Parameters.Combo Result = 3 OR Cross Level Parameters.Combo Result = 7 THEN Source Value * Product Count ELSE Source Value
IF Cross Level Parameters.Combo Result = 3 OR Cross Level Parameters.Combo Result = 7 THEN Source Value * Product Count ELSE Source Value
@SnehaPrabha It sounds like you want UPT to:
If that's the case, a simple solution is to have three line items:
Here's what the blueprint looks like:
And here's what the output in my model looks like (where region and quarters average but product sums across the active dimension):
If you want summary method behavior to be different within different levels of the same hierarchy, you need to get more complicated. (Similar to what @nvilkko described)
@rob_marshall I feel like you would be able to answer my question, and if you can't then no one can. My question is - using selective access on a hierarchy level I want to be able to look up the user that has access to a particular level within the hierarchy so that I can create a standardized e-mail using the mailto…
I have difficulty with “Custom views” in Activities 13.6.5 and 13.9.3 where I select Custom View but cannot Filter Time Dimension. The instruction is "Filter: Time - show only Forecast Months" Could anyone please help on this issue.
Hi! I'm currently trying to set up an example for the forecaster and I'm running into various issues. At first (and I managed to resolve it by now) my data collection creation failed without any error messages. Next, after I managed to create it, my forecaster model is stuck on status 'creating' for 1.5 hours already. Is…
