Equivalent to SUMPRODUCT in Pyramid
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
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.
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!