Common calculated measure across all dimensions

Question

I have a cube with 4 dimensions and I have a measure called Transaction Count. Now I want to calculate the Percentage across all the dimensions for that above measure.

I also have a dimension called Cars. I have the count across all the Cars and now I have defined a calculated measure for calculating the Percentage of each car from the total number of transactions. But it will work only for that particular dimension.

How I can create a single percentage calculated measure which can be used across all the dimensions?

MDX for the calculated measure: (which is working for only Carmake dimension)

CASE
    WHEN ISEMPTY( [MEASURES].[Trans COUNT] ) 
    THEN 0
    ELSE ([Dim Car Make].[Hierarchy].CURRENTMEMBER,
           [MEASURES].[FACT COLORPERFORMANCE COUNT])/
         ( [DIM CAR MAKE].[CARMAKE].[(ALL)].[ALL], 
           [MEASURES].[Trans COUNT])
END

I already have a Trancount(1000) measure. Now I need to create a calculated measure Freq % which should be calculated across all the dimensions.

Screenshot -> http://i.stack.imgur.com/iuaQO.jpg (need 10 rep for posting images)

Table 1 in screenshot - you drag and drop the carmake dimension, then both Tran Count and Freq% should be calculated as per CarMake breakdown.

Table 2 in screenshot - you remove CarMake and drag Quality, then both Tran Count and Freq% should be calculated as per Quality breakdown.

Table 3 in screenshot - you remove Quality and drag Brand, then both Tran Count and Freq% should be calculated as per brand breakdown.

could be possible - please add your current mdx attempt to the question — whytheq
You will need a calculation dimension. There is a lengthy example in Chris Webb's Expert Cube Development. If you cannot look into that, let me know and I will explain it in more detail when I'm at work. — George T
@whytheq: I have updated the question with relevant MDX and also added the screenshot of what I would need to happen. — BalajiB
@GeorgeTanasie: Sorry at this moment I cannot get the book as it is out of stock locally. I provided more details to the questions if it may help you. Please look into that. — BalajiB

OrenHenig OrenHenig · Accepted Answer · 2015-05-21T18:36:59

The best way i found is using AXIS() to dynamically get the currently used dimension.

with
member Member_Lvl as AXIS(0).item(0).level.ordinal --get the level for next calc
member All_Member as sum(ancestor(axis(0).item(0).hierarchy.currentMember,<yourMeasure>),Member_Lvl )
member Percent_of_All as sum(axis(0).item(0).hierarchy.currentMember,<yourMeasure>) / All_Member 

select <Your Dim > on 0, {<Your Measure>, Percent_of_All} on 1
from <Your Cueb>

NOTICE: replace the SUM function on the calculations if you need other aggregation.

Common calculated measure across all dimensions

1 Answers