One formula, mathematical operation on one dimensional value, then add together?

[Anonymous]

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

[Anonymous]

I don't need to have it collapsed. The table is locked. The data set has multiple KPIs as rows with each fitting in a category and each having a different formula. There is a specific sort applied to the order of the KPIs as well using a separate dimension between the category and the KPI. For ease of maintenance the developer uses one expression for actual leveraging a Pick(Match()) combo to select the correct formula. The table as three dimensions across the top. When I use this expression standalone it works but if I use it in the table it does not. I'm thinking of adding the first level aggregation to the script so I only have to add and divide. This is driving me nuts and the application really needs to be rewritten.

Maybe a data set like this...

Month	Area	KPI	Customer	Category	Group	Scenario	Value
Jan	Marketing	Population	A	Male	1	Actual	45
Jan	Marketing	Population	A	Male	2	Actual	19
Feb	Marketing	Population	A	Male	1	Actual	10
Feb	Marketing	Population	B	Male	2	Actual	30
Jan	Marketing	Population	A	Female	1	Actual	23
Jan	Marketing	Population	B	Female	2	Actual	4
Feb	Marketing	Population	B	Female	1	Actual	20
Feb	Marketing	Population	B	Female	2	Actual	8
Jan	Marketing	Weight	A	Male	1	Actual	2
Jan	Marketing	Weight	A	Male	2	Actual	5
Jan	Marketing	Weight	A	Female	1	Actual	1
Jan	Marketing	Weight	B	Female	2	Actual	10
Feb	Marketing	Weight	A	Male	1	Actual	4
Feb	Marketing	Weight	B	Male	2	Actual	3
Feb	Marketing	Weight	B	Female	1	Actual	19
Feb	Marketing	Weight	B	Female	2	Actual	7
Jan	Design	Volume	A	Male	2	Actual	100
Jan	Safety	Sales	A	Male	2	Actual	0.54
Feb	Design	Volume	A	Male	2	Actual	1040
Mar	Design	Volume	A	Male	2	Plan	109
Feb	Safety	Sales	A	Male	2	Actual	100.54
Jan	Design	Losses	B	Female	1	Actual	-503.00
Jan	Marketing	Losses	B	Female	1	Actual	-605.90
Jan	Marketing	Losses	B	-	-	Comment	Unknown
Jan	Marketing	Population	A	-	-	Comment	Male customers played a larger role in our monthly metrics.
Jan	Design	Losses	B	-	-	Comment	Losses were higher than normal

		A				B
		1		2		1		2
		Male	Female	Male	Female	Male	Female	Male	Female
Marketing	Population	weighted
	Losses	sum
Design	Volume	straight avg
	Losses	sum
Safety	Sales	sum

[sunny_talwar]

It might help if you can share the application where you have the chart where we can see what you have and what you are trying to accomplish... I can only give you a solution for something you provide... if we are going to incrementally add things, we might never finish this discussion. So, I request you to provide a somewhat complete sample app and point out what isn't working for you.

Best,

Sunny

[Anonymous]

I understand and agree Sunny. I cannot share the application due the sensitivity of the data and design. I will need some time to come up with a different set of data for which I can replicate the issue. Honestly, I am pushing for the entire design to change. I truly appreciate your remarkable insight and assistance.

[sunny_talwar]

Sounds good... in the mean time, if confidentiality and sensitivity of the data is the only concern that you have, please spend some time to look at this

Preparing examples for Upload - Reduction and Data Scrambling

This may help you address those concerns..

Best,

Sunny