January 28, 2002 at 12:56 am
I have a cube with two measures "Margin Percent" and "Order Quantity" respectively.
"Order Quantity" has the SUM aggregate function as I want the Quantities to be added when I go to higher levels.
As for "Profit Margin", I want to aggregate it by calculating the Weighted Average using "Order Quantity" as a weight. The formula for this is
Weighted Average ("Profit Margin") =
[Sum("Profit Margin" * "Order Quantity")] / [Sum("Order Quantity")]
When I am at the leaf level the "profit margin" is correct. However if i am higher, I have to go to the leaf level to calculate the formula for all the descendants.
Has anyone come across this problem?
-----------------------------------------------------------------------------
PS: For example
Profit Margin | Order Quantity | (Profit Margin * Order Quantity)
1% | 100000 | 100000
2% | 1000 | 2000
10% | 100 | 1000
------------- -------------- -------------------------------
| 101100 | 103000
Weighted Average =(103000 / 101100) = 1,01879
May 26, 2011 at 5:34 pm
Hi,
Weighted averages are tackled at the bottom. To overcome your non leaf level wrong values, you have to create a calculated column in your fact table (DSV)
Step 1
Add a column in your fact table view, or (Calculated column in DSV) thus in the Relational database, holding the product of
[Profit Margin] and [Order Quantity]
looks like this
, [Profit Margin] * [Order Quantity] AS [Profit Margin OQW] --OQW = Order quantity weighted..
Step 2
Add this one as a new measure to your cube 'Aggregate function' = SUM
Step 3
Create a Calculated measure in your cube calculations tab
name [OQW Profit Margin]
IIF ( ISEMTPY ([Measures].[Order Quantity]) OR [Measures].[Order Quantity] = 0 , NULL , [Measures].[Profit Margin OQW] / [Measures].[Order Quantity] )
Hopes this helps
July 3, 2019 at 2:21 pm
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply