how to average on roll up


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.


  • @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

  • Dikshant
    edited January 11

    @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

    → 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

  • @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)