MDX: Percent of Specific Account Member

Joined: July 3rd, 2012, 12:30 pm
Hi,

I am new to MDX and despite my research I'm struggling with an MDX formula. I'd like to calculate the % of lower level accounts to the sales account ("%REV"). This will be used as a member formula and will be used in the following example:

_______________JAN__________FEB
_______________ACT__%REV___ACT__%REV
ACCT-49999_____100__100%____90___100%
ACCT-60110_____60___60%_____60___67%
acct-60120______10___10%_____9___10%

Since I'm new I'm not sure if I've provided enough information to be helpful. Happy to fill in any gaps.

I'd appreciate any direction that can be provided.
Thank you.

Joined: November 16th, 2003, 11:27 pm
I can probably help with this, but I don't understand your example.

You show Jan Act for 49999 is 100 and %Rev is 100%, then there are other members that I assume are siblings. If you sum the siblings you get 170%. In Feb the Act number goes to 90 but still stays 100% and for 60110 the number stays 60 but the percent goes to 67%. I have no idea how the math is working here. What are your numerators and denominators?

Joined: July 3rd, 2012, 12:30 pm
Hi Gary,

Here is the hierarchy of the account dimension.

ACCT-49998-Contribution
_____ACCT-49999-Revenue
_____ACCT-69999-Expenses
__________ACCT-60100-Wages Parent
_______________ACCT-60110-Regular Wages
_______________ACCT-60120-Vacation Wages
[other 6XXXX accounts below]

What we are wanting to calculate are the % of Acct-49999. In my previous example this is why is was 100% (e.g 1/1)

The 6XXXX accounts are all of under the expense parent (which is a sibling).

So you are right that the %'s are calculated vertically and the denominator is always ACCT-49999.

Thanks again!

Joined: November 16th, 2003, 11:27 pm
The simplest way to do this is to add an Analytics dimension. The idea is to add a dimension with one stored member [Input] and then you can base your calcs against that member so in your case it would be

%Rev: [Input] / ([Input], [ACCT-49999]) * 100

This is the easiest way to do it because it only requires one formula and then any account in the acct dimension will calc against Revenue. The downside is if this is a cube that is already built adding a new dim can be an issue with some existing reports, although in most cases it should be fine. In that case you may want to try to leverage another dim like Scenario for instance, where you might be able to sneak it in.

The other alternative is to put the formulas in the accounts dim, but then you have the issue of having to create each calc individually.

i.e.

Expense Rev %: [ACCT-69999] / [ACCT-49999] * 100
Wages Rev %: [ACCT-60100] / [ACCT-49999] * 100

etc etc etc

The issue here is you have to create a calc member for each member. Some people find that to be easier for end users than an analytic dim, which may be a little challenging to some users if they do not understand multi-dim real well. So you could go either way. Mocking this up in Excel and using a load rule to add the calc members certainly wouldn't be that difficult.

To be clear though, there is no way to create a single member in the account dim to do this dynamically. There would be no way for the function to know what member you want to use as the numerator.