Percentage of Total

Hi Friends,

I have a Cube wherein i have dimensions with following Heirachy:-

Activity

PartNumber

Now if the person selects Activity and Quantity, like as below i want output as

Activity Part Quantity %of Quantity

Activity1 Part1 75 37.5%

Part2 125 62.5%

Activity1 Total 200 40%

Activity2 Part3 90 90%

Part4 10 10%

Activity2 Total 100 20%

Activity3 Part5 100 50%

Part6 100 50%

Activity3 Total 200 40%

This is really urgent...

Regards,

Kaushal

Here's a sample Adventure Works query which may help:

With

Member [Measures].[OrderPercentOfParent] as

[Measures].[Order Quantity] /

([Measures].[Order Quantity],

[Promotion].[Promotions].Parent),

FORMAT_STRING = 'Percent'

select

{[Measures].[Order Quantity],

[Measures].[OrderPercentOfParent]} on 0,

NON EMPTY DrillDownLevel([Promotion].[Promotions].[All Promotions].Children) on 1

from [Adventure Works]

Order Quantity OrderPercentOfParent
No Discount 238,806 86.91%
Reseller 35,970 13.09%
Discontinued Product 838 2.33%
Excess Inventory 304 0.85%
New Product 2,356 6.55%
Seasonal Discount 1,172 3.26%
Volume Discount 31,300 87.02%

|||

Deepak thanks for the help, but i have used following formula...

IIF([Activity].CurrentMember.Parent is null, 100,(Measures.[Net OI EUR - 2006] / (Measures.[Net OI EUR - 2006],[Activity].CurrentMember.Parent))*100)

Activity Value - 2006 Value - 2007 Var% Delta Mix(Value2007 - Value2006) * Var%

Act1 100 75 5% -0.125

Act2 50 80 10% 3

Act3 50 80 3% 0.9

Grand Total 200 235 4% 1.4(But i want 3.775 Sum of Act1+Act2+Act3)

How can i achieve this, really urgent...

Regards,

Kaushal