Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello community,
I am struggling with even figuring out what to use for a search string when researching this topic. Basically, I have a situation where I need to perform a multiplication against each dimensional value then add each resulting value together, finally dividing by the sum of another set of values. I am not able to see a clear path through to this but know it should work. Your guidance is greatly appreciated.
Let me illustrate...(a weighted average)...
Formula: ((P1*W1) + (P2*W2)) / (W1+W2)
Let's put some real values behind this...
Group | Gender | Population | Weight |
---|---|---|---|
1 | Male | 45 | 2 |
1 | Female | 23 | 1 |
2 | Male | 19 | 5 |
2 | Female | 4 | 10 |
(SUM(Population)*SUM(Weight)) / SUM(Weight)
Does not give the correct value because it will add all of the populations and add all the weights finally multiplying the two values before dividing by the weights (which would mathematically negate the multiplication anyhow).
What we need to do is:
((45*2) + (23*1) + (19*5) + (4*10)) / (2+1+5+10)
= ((90)+(23)+(95)+(40)) / (18)
= (248) / (18)
= 13.78
The total weight averages of all genders.
Furthermore, I then need the same formula to work if I create a chart object with the dimension Gender and the total weighted average (irrespective of Group):
Male weighted: ((45*2)+(19*5)) / (2+5) = 26.43
Female weighted: ((23*1)+(4*10)) / (1+10) = 5.73
Gender | Weighted Average |
---|---|
Male | 26.43 |
Female | 5.73 |
Finally, you would be able to use the same formula is showing the Group and total weighted average (irrespective of the Gender):
Group 1 weighted: ((45*2) + (23*1)) / (2+1) = 37.67
Group 2 weighted: ((19*5) + (4*10)) / (5+10) = 9.0
Group | Weighted Average |
---|---|
1 | 37.67 |
2 | 9.0 |
And you get the idea. One formula, any slice as long as we use the Population and the Weight in the formula.
Mike
Message was edited by: Michael Forte - Corrected a typo
Try this
=Sum(Aggr(Sum({<Scenario = {'Actual'}, KPI = {'Population'}>} Value) * Sum({<Scenario = {'Actual'}, KPI = {'Weight'}>} Value), Category, Group))/Sum({<Scenario = {'Actual'}, KPI = {'Weight'}>} Value)
try this expression
=sum(Population*Weight)/sum( Weight)
Check this?
Close, but there is a thicker plot at play here -- the data is not structured well and I am stuck with that for now...
The data set does not have just these values in it. There is a [Value] field that contains values for many different KPIs -- population and weight being just two. There is a [Scenario] field that contains an identifier for whether [Value] is plan, actual, or a comment. So, we need to apply set analysis to the equation which will "pick" the correct value.
KPI | Category | Group | Scenario | Value |
---|---|---|---|---|
Population | Male | 1 | Actual | 45 |
Population | Male | 2 | Actual | 19 |
Population | Male | 1 | Plan | 10 |
Population | Male | 2 | Plan | 30 |
Population | Female | 1 | Actual | 23 |
Population | Female | 2 | Actual | 4 |
Population | Female | 1 | Plan | 20 |
Population | Female | 2 | Plan | 8 |
Weight | Male | 1 | Actual | 2 |
Weight | Male | 2 | Actual | 5 |
Weight | Female | 1 | Actual | 1 |
Weight | Female | 2 | Actual | 10 |
Try this
=Sum(Aggr(Sum({<Scenario = {'Actual'}, KPI = {'Population'}>} Value) * Sum({<Scenario = {'Actual'}, KPI = {'Weight'}>} Value), Category, Group))/Sum({<Scenario = {'Actual'}, KPI = {'Weight'}>} Value)
I figured this would need an Aggr() and I just tested with it. Thanks!
Sunny,
I have a new challenge and hopefully I created a data set that works for the test. Convert the table to a pivot as below, where the KPI population is weighted with a data set that has two months. I don't think we want to aggr() by month, do we?
1 | 2 | |||
Male | Female | Male | Female | |
Population | 21.67 ((45 * 2) + (10 * 4)) / (2 + 4) | 20.15 ((23 * 1) + (20 * 19)) / (1 + 19) | 23.13 ((19 * 5) + (30 * 3)) / (5 + 3) | 5.65 ((4 * 10) + (8 * 7)) / (10 + 7) |
Month | KPI | Category | Group | Scenario | Value |
---|---|---|---|---|---|
Jan | Population | Male | 1 | Actual | 45 |
Jan | Population | Male | 2 | Actual | 19 |
Feb | Population | Male | 1 | Actual | 10 |
Feb | Population | Male | 2 | Actual | 30 |
Jan | Population | Female | 1 | Actual | 23 |
Jan | Population | Female | 2 | Actual | 4 |
Feb | Population | Female | 1 | Actual | 20 |
Feb | Population | Female | 2 | Actual | 8 |
Jan | Weight | Male | 1 | Actual | 2 |
Jan | Weight | Male | 2 | Actual | 5 |
Jan | Weight | Female | 1 | Actual | 1 |
Jan | Weight | Female | 2 | Actual | 10 |
Feb | Weight | Male | 1 | Actual | 4 |
Feb | Weight | Male | 2 | Actual | 3 |
Feb | Weight | Female | 1 | Actual | 19 |
Feb | Weight | Female | 2 | Actual | 7 |
This
=Sum(Aggr(Sum({<Scenario = {'Actual'}, KPI = {'Population'}>} Value) * Sum(TOTAL <Category, Group, Month>{<Scenario = {'Actual'}, KPI = {'Weight'}>} Value), KPI, Category, Group, Month))/Sum(TOTAL <Category, Group>{<Scenario = {'Actual'}, KPI = {'Weight'}>} Value)
Thanks Sunny. I am not able to get this to work with my data set and pivot table. Does the aggr() need to match the pivot dimensions across the top?
I believe so... but won't really know if you have more or less dimensions... the above might not work if you have collapsed your dimensions as well... do you need them to work even when collapsed? What would be the expected number you would expect to see when collapsed?