Solve Weighted Averages Problem in SAP BW Warehouse

[BRYNEVANS]

Column-----> A------- B-------C-------D---------E
Beds------> 21.0-- 181125 - 0.0001 - 95600 -- 11.08405797
Balls------> 180 --- 39255 -- 0.0046 - 17715 -- 81.23041651
Results----> 201---220380 - 0.0009 - 113315 - 103.3501906
sum sum X/Y sum CxD
I have the above problem on a program I am writing and need the mathmatical solution to get the right results row. This program is used in the SAP BW Warehouse software. The program deliveries the above result. It calculates the the result row by the formula below the results row. What I want to get to is for column E to sum to 92.31447 and not 103.350. The program for programmable reasons cannot sum the two rows so I need to derive a formula with the figures above to get to the desired result of 92.31447
Any help will be appreciated. I am just about off my head!
Cannot get the formating correcthence the ---. Basically I have 5 columns A-E.

 

[HallsofIvy]

I THINK what you mean is that the "Results" row under the "A", "B", and "C" columns is the sum of the two numbers above it, but I have no idea what X/Y means since there is no X and Y. I also don't know what CxD means since "C" and "D" are columns of numbers, not numbers. I'm wondering how in the world the number of "Beds" could be 0.0001 or how the number of "Balls" could be 0.0046!

In any case, all you are telling us is that you WANT 92.31447 in the last column without telling us WHY or how that number is supposed to be connected with the others?? One perfectly valid formula for that result is "92.31447"- in other words whatever the other numbers put 92.31447 in that place. Without knowing what in the world you are doing or what these numbers are supposed to MEAN that's about the best we can do.

 

[tacman]

Column A contains the item names, column B contains the number of beds, column C contains the weight of the beds, column D contains the number of balls, and column E contains the weight of the balls. The results row calculates the total number of items, the total weight of all items, the weighted average of the items (X/Y), and the total weight of the items multiplied by the weighted average (CxD).

To solve this problem in SAP BW Warehouse, you can use the following formula:

Weighted Average = (Sum of (Number of items * Weight of items)) / (Sum of Number of items)

In this case, for column E to sum to 92.31447, the formula would be:

92.31447 = (Sum of (Number of beds * Weight of beds) + Sum of (Number of balls * Weight of balls)) / (Sum of Number of beds + Sum of Number of balls)

Using the given data, the calculation would be:

92.31447 = ((21 * 0.0001) + (180 * 0.0046)) / (21 + 180)

92.31447 = (0.0021 + 0.828) / 201

92.31447 = 0.8301 / 201

92.31447 = 0.0041248756218905472636815920398

Therefore, to get the desired result of 92.31447 in column E, you would need to adjust the weights of the beds and balls accordingly. You can also use this formula to calculate the weighted average for any other set of data in SAP BW Warehouse.