0

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

3replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • 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

    Like 3
  • 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

    Like
  • 🙂

    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 

    Like
Like Follow
  • Status Answered
  • 5 mths agoLast active
  • 3Replies
  • 38Views
  • 2 Following