I need to create a calculation in Bex for weighted average.
For example Item 1 has a price of $10 and 4 items sold.
Item 2 has a price of $15 and 3 items sold.
The weighted average would be calculated by first calculating the total sales dollars for each item, summing these together for the result total dollars, and then dividing that total by the total quantity of items sold.
So in bex I have created a calc key figure in one column which is Price x items sold to get total sales dollars. Then I have formula in another column to divide total sales dollars by total items sold. The problem is that in the last column the calculated single values are ok, but in the result row the calculated price for item 1 and item 2 are being added together.
I have tried several things with settings for single values and results and have not been able to get the result row to calculate the correct weighted avg price.