April 18, 2003 at 1:44 pm
I am having problems with a calculated member in my cube. I am attempting to display a dimension as a percentage of the total.
For example, I have a dimension called system with 5 unique values (A,B,C,D,E). I want to see the values rolled up to 100%. I have this working but I must hardcode the dimension "System" in order to get it to work. "Count" is a measure of the cube.
I have other dimensions on the cube so I want the percentage to be dynamic for whatever dimension I choose. Below I have my current syntax in the calculated member.
[Count] / ( [Count] , Ancestor([System].CurrentMember , 1) )
Please help!
April 23, 2003 at 5:09 am
Hi Hfrank,
Such an easy request but so hard to do....
I spent ages looking at how to do this at design time (I wanted this measure too!) but with no success. I thought maybe the 'Axis(n)' function would solve this, but it looks like this requires some definition to enable it to resolve. Basically, I can't find any function that allows you to access (in a calc member) the "current" row or columns (other than Axis()), so design time looks like a non-event.
Have you tried posting to the microsoft.olap newsgroup? George Spofford (look for his MDX Solutions book, really quite good) responds to that newsgroup regularly, and he would be my first port of call for a solution.
We do this in our front-end tool (as does basically everyone else). WHat front end are you using? If you're using Excel, a little user training (or a saved and distributed report) will let you do this.
To get the percentages in Excel, right click on the measure place holder in the crosstab, then select 'Field Settings', then chose the 'Options' button. This should show a 'Show Data as' dropdown list, then select (for e.g.) '% of row', and the measure will show as you need.
HTH,
Steve.
Steve.
April 24, 2003 at 10:55 am
I did recieve the answer from another post. Thanks to all who responded.
==> PASS December 2002
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply