1

Equivalent to SUMPRODUCT in Pyramid

Hi

 

I have to do a weighted average calculation to calculate a market average. In excel I would use sum product where I can't get the numeric value for the %'s and created a weighted average.

 

I have a set of premium values, and a set of % values.

 

Rather than try to explain poorly it I've attached a workbook example, could the output in h5:j8 be replicated in pyramid using data from columns A:E

 

Thanks

 

Nick

3 replies

null
    • Senior Director of Product
    • Ian_Macdonald
    • 1 yr ago
    • Reported - view

    Hi Nick,

    There are two ways of doing this, depending on your model structure.

    1. Using two flat dimensions

    We need to get the list of shops for each type, for each shop calculate profit*ratio, then sum that and divide by the sum of profit.

    We can get the list using the NONEMPTY function across lists of Type and Shop. Then SUM this list for Profit*Ratio, then divide by SUM profit thus:

    Creating a grid with Year and Type in the rows, we can then drop in the Weighted Average Measure:

     

    2. Using a Regular Hierarchy

    If we construct a Regular Hierarchy, Type --> Shop in the Model, we can use the implicit grouping  in the hierarchy to select the list of Shops for each Type (the equivalent of the NONEMPTY() function). We need to do this for each row in turn, so we use the CURRENTMEMBER() function to execute this process for each Row of Type, and the CHILDREN() function to get the list of Shops for that Type:

     

    Place the Hierarchy on the Rows at the Type level and drop in the calculated weighted average Measure:

     

    Hope that helps.

    Ian

    • Nicholas_Campbell
    • 1 yr ago
    • Reported - view

    Ian - i have a feeling level 3 in the learning hub wont be a problem for you :)

     

    many thanks! you've saved me a lot of time

    • Senior Director of Product
    • Ian_Macdonald
    • 1 yr ago
    • Reported - view

    🙂

    Funny you should mention that, we're currently preparing for SOC II certification. Part of the audit is that every employee has to have taken product training through the LMS. So I have go do it...

    Further to the above, for the flat hierarchies I've realised you don't need the NONEMPTY() function, as the context of the query defines the scope for the SUM anyway. So you can simplify it thus:

    Eagerly awaiting your next challenge!

    Cheers!

    Ian 

Content aside

  • Status Answered
  • 1 Likes
  • 1 yr agoLast active
  • 3Replies
  • 104Views
  • 2 Following