Need Help on Calculated Dimension

[agni_gold]

Hi all ,

I have a situation ,

I have made a one pivot table it seems like

Account	Value
A	10
B	20
C	30
D	40
E	50
F

Account  is my one dimension and value is my second calculated dimension , which is calculated by

below calculated expressiob

=(round(aggr(sum(Value),Account),0.01))

But now I want to show in front of F is  the division of E/A . I have tried many ways , please suggest me any answer.

[MK_QSL]

=ROUND(Aggr(IF(Account = 'F', SUM(TOTAL {<Account = {'E'}>}Value)/SUM(TOTAL {<Account = {'A'}>}Value),SUM(Value)),Account),0.01)

[sunny_talwar]

Are you looking to do this:

Try this expression as your calculated dimension:

=If(Account = 'F',

Num(Only(TOTAL {<Account = {'E'}>}Aggr(Sum({<Account = {'E'}>}Value), Account))/

  Only(TOTAL {<Account = {'A'}>}Aggr(Sum({<Account = {'A'}>}Value), Account)), '#,##0.00'),

Aggr(Sum(Value), Account))

HTH

Best,

Sunny

[avinashelite]

You want a static calculation or dynamic? i.e. you always wanted the division of A/E for the F??

[agni_gold]

I have written following expression already , so can you please help me to inbuilt your expression with your logic.

I need this condition must in calculation if([Account Type]=[Entry Type]

=aggr(sum(if([Account Type]=[Entry Type],Value))/1000000,Account)

[MK_QSL]

I am confused now... What you have asked now is that same of your original question? or it is something like 2nd question?

[agni_gold]

Yes it is some kind of second question but related to this  :

Now i have one expresion

sum(Value)/1000000

now i want to do same thingh in expression also , i have written same as in above answer but it is not working .

[agni_gold]

Red square are my dimesion for which i have asked question , but after that i have added one expression

sum(Value)/1000000

and in this pivot table i have one dimesion region and i have draged to horizontaly , now i want to show the division as i asked before. For each region .

Nut i am applying the same expression as in above answer but it is not working . Please help.

I am using

=If(Account = '%Operating',

Sum({<Account = {'Income'},[Value] = {"=[Account Type]=[Entry Type]"}>}Value/1000000)

/

Sum({<[Account = {'Total'},             Value = {"=[Account Type]=[Entry Type]"}>}Value/1000000)

,

round(Sum({<Amount = {"=[Account Type]=[Entry Type]"}>}Value)/1000000,0.01))