Calculated member - please help (SQL Server 2000)

hi,

please if u could help with this:

i have a cube with 3 dimensions, Sales (fact table), Time and Accounts (general ledger accounts). Accounts dimension hierarchy is as follows:

1. Revenues
1.1 Income
1.1.1 number of account in General Ledger
1.1.2 number of account in GL etc.....
2. Expenses
2.1 Fixed costs
2.1.1 number of account in GL etc....
2.2 Operating costs
2.3 Overhead
3. Unsorted

now what i need to do is next: i need to calculate measure.salesamount in percentage of total sales amount for each category of accounts. for example i need measure.salesamount of each account number in Revenue/Income in percentage of the total salesamount of the category Revenue.

i hope i was clear enough in what i need.

please can anyone help me with this?

thank you in advance!

marko

try an expression something like the following in your calculation:

Measures.salesAmount / (Measures.salesAmount,Accounts.CurrentMember.Parent)

You can then either wrap the whole thing in brackets and mulitply by 100 or you should be able to use a percentage style format string - it depends a bit on how your front end works.

|||Hello Marko,

i am not 100% clear on ur pblm. but on what ever i understood i have given u a formula for calculating the calculated-member.

iif(
[revenue_dimension].currentmember.parent is null,1, ( [revenue_dimension].currentmember , [measures].[salesamount] ) / ([revenue_dimension].currentmember.parent , [measures].[salesamount] )

)
then make the data type of this calculated-member to percentage.

regards,
Rajiv.|||

Hi,

thank you both for your answers. both formulas work and give the same result.

Thank you once again for your help! Much appreciated!

best regards

marko