how to average on roll up

SnehaPrabha

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?

SRoberts

Try changing your summary method from "Sum" to "Average"

HimanshuRaj

@SnehaPrabha You can use Average Summary for that line item from blueprint of the module.

Cheers!!

Dikshant

@SnehaPrabha As everyone mentioned, try this out! Changing the Summary method and explore bunch of other summary methods!

SnehaPrabha

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.

SnehaPrabha

I also want to let you know that I also have quarterly summary in the same module

SnehaPrabha

Sum and Time: Average work well when I chose country but when I chose region it shows all country numbers added up

Dikshant

@SnehaPrabha - Can you pivot and put the country dimension in rows and show where, for two countries, it shows SUM at the region level?

nvilkko

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

→ Summary should be SUM

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

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

JMVasicek

@SnehaPrabha It sounds like you want UPT to:

• Sum across Products
• Average across Time (at quarters, years, etc.)
• Average across Geo (at Region, etc.)

If that's the case, a simple solution is to have three line items:

1. Value (Divisor)
   1. Formula: 1 (a simple count to calculate the divisor)
   2. Summary Method: SUM
   3. Dimensions: Only the dimensions you want to average (which sounds like Country and Time)
2. Value (Sum)
   1. Formula: [your calculation or user input]
   2. Summary Method: SUM
   3. Dimensions: All dimensions relevant to this metric
3. Value (Output)
   1. Formula: =Value(SUM) (this looks like a daisy chain, but it's necessary in order to summarize correctly)
   2. Summary Method: RATIO ( Value (SUM) / Value (Divisor) )
   3. Dimensions: All dimensions relevant to this metric

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)