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

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