Conditional Calculation Script Based on Roll Up

I'm working on an MDX Calculation Script that needs to be dynamic depenind on how it is being rolled up. I've never attempted anything like this before, so if anyone else out there has done anything similar to this, I would appreciate you input.

My problem is that I need to create a calculation that changes based on how the dimension the end user is rolling up by. To clarify, I need to have 3 different calculations within the same measure. If the end user is rolling the measure up by Test, I need to see calculation 1, but if the end user is rolling up by Grade, I need to see calculation 2, and if the end user is rolling up Location, I need to see calculation 3.

The measures needed to complete this puzzle are not actually in the cube yet, so I haven't really had a chance to tackle this. I've just been researching and brainstorming for now. Based on everything I've looked out, I think I may be able to do this using a Scope statement, althought I'm not 100% sure this is the way to go since I'm not very well versed in MDX.

I'd appreciate any bright ideas. Thanks, guys.

I would like to get a little clarity.  First, it seems that Test > Grade > Location is a hierarchy.  Is this a correct statement?  Second is the calculation the same for each level of the hierarchy, or do you expect it to be a completely different calculation for each level.  If it is a hierarchy, and the formula is the same except for the scope being calculated, you should be good using a scope statement.

It is NOT a hierarchy. These are different dimensions. What I basically want to happen is when I slice the measure by the dimLocation, it calculates one way. And when slicing the measure by Grade, it calculates another way, and the same for Test.

Also, the calculation is different for each one, but only slightly. So when rolling up by test, I need to see the measure calculated using Formula A. When rolling up by Grade, I need to see the measure calculated using the Avg(Formula A), and when rolling up by Location, I need to see the measure calculated using the Avg(Avg(Formula A)).

I know its confusing and it may not be possible, but I wanted to get some feedback from people more knowledgeable than I in MDX and SSAS.

Dustin

Based on your information I think you could do it with something like this

SCOPE ([Measures].[Multiuse Calculated Measure], XXX);
where XXX can be any set like [DimName].[Grade].members or a named set

Note that XXX cannot be an arbitrary shaped set.  SSAS will give you an error.
From http://msdn.microsoft.com/en-us/library/bb934106.aspx Arbitrary shaped set:  A space that cannot be expressed as the cross join of two or more sets. For example, the space {(Drink, USA), (Food, Canada)} represents an arbitrary shape because it is a subset of the cross join between {Drink, Food} * {USA, Canada} = {(Drink, USA), (Drink, Canada), (Food, USA), (Food, Canada)}. You may not run in to this but this was a real head scratcher from me the first time i saw it.

Your MDX Script would look something like this and only one measure would be seen that would act differently when crossed with different dimension attributes

CREATE MEMBER CurrentCube.[Measures].[Multiuse Calculated Measure] AS NULL;

SCOPE ([Measures].[Multiuse Calculated Measure], [DimName].[Grade].members);
  this = Formula A;
END SCOPE;
SCOPE ([Measures].[Multiuse Calculated Measure], [DimName].[Test].[Test].members);
  this = Formula B;
END SCOPE;
SCOPE ([Measures].[Multiuse Calculated Measure], [DimName].[Location].[Location].members);
  this = Formula C;
END SCOPE;

It becomes more tricky when you include the All member in the set.  The All member in this case will use Formula A.  Experiment because if you start getting unexpected results remove the All member from the scoped set as see how it affects your results.  In the above example change [DimName].[Grade].members to [DimName].[Grade].[Grade].members.

Regards,

Luke

One more thing Dustin,

I was assuming in my post that you created a grade dimension (0-100).   Typical I would think of a grade as a measure

Regards,

Luke

Sorry I haven't responded earlier, but I was able to have success with my MDX script. I ended up using a series of SCOPE statements embedded in each other so I could specify a calculation for one sub cube and then use another SCOPE statement to further narrow down the initial sub cube and then specify another calculation for the second subcube and so on. It seems to work pretty well. It looked something like this:

//For the Actual QPI Calc measure:
        SCOPE ([Measures].[Actual QPI Calc]);   
        THIS = NULL;   

//If slicing only by Dim Mill,
            SCOPE ([Dim Mill].[Mill].members);   
//get the average based on the Grade numbers.
            THIS = AVG([Dim Mill].[Mill].members,
                        (sum([Measures].[Num Inspec])/sum([Measures].[Num Tests])));   

//If slicing only by Dim Mill and Dim QPI Grade,
                SCOPE ([Dim QPI Grade].[Grade Name].members);   
//use this calculation. 
                THIS = (sum([Measures].[Num Inspec])/sum([Measures].[Num Tests]));   
                
                END SCOPE;   

//If slicing only by Dim Mill and Dim QPI Test,
                SCOPE ([Dim QPI Test].[Short Name].members);   
//use this calculation. 
                THIS = (sum([Measures].[Num Inspec])/sum([Measures].[Num Tests]));   

//If slicing only by Dim Mill, Dim QPI Test, and Dim QPI Grade,
                    SCOPE ([Dim QPI Grade].[Grade Name].members);   
//use this calculation.                    
                    THIS = (sum([Measures].[Num Inspec])/sum([Measures].[Num Tests]));   
                    END SCOPE;   

                END SCOPE;   

            END SCOPE;   
            
//If slicing only by [Dim Mill].[Location],
            SCOPE ([Dim Mill].[Location].members);   
//get the average based on the Mill and Grade numbers.
            THIS = AVG([Dim Mill].[Mill].currentmember,
                    AVG([Dim QPI Grade].[Grade Name].currentmember,
                        (sum([Measures].[Num Inspec])/sum([Measures].[Num Tests]))));   
            END SCOPE;   

        END SCOPE;   

//------------------------------------------------------------------------------------------------//
    
//For the Standard QPI Calc:
        SCOPE ([Measures].[Standard QPI Calc]);   
        THIS= NULL;   

//If slicing only by Dim Mill,
            SCOPE ([Dim Mill].[Mill].members);   
//get the average based on the Grade numbers.
            THIS = AVG([Dim Mill].[Mill].currentmember,
                    (SUM([Dim Mill].[Mill].currentmember, 
                        ([Measures].[Num Tests x Standard QPI]))/
                    SUM([Dim Mill].[Mill].currentmember,
                        ([Measures].[Num Tests]))));   

//If slicing only by Dim Mill and Dim QPI Grade,
                SCOPE ([Dim QPI Grade].[Grade Name].members);   
//use this calculation. 
                THIS = AVG([Dim QPI Grade].[Grade Name].currentmember,
                        (SUM([Dim QPI Grade].[Grade Name].currentmember, 
                            ([Measures].[Num Tests x Standard QPI]))/
                        SUM([Dim QPI Grade].[Grade Name].currentmember,
                            ([Measures].[Num Tests]))));   
                
                END SCOPE;   

//If slicing only by Dim Mill and Dim QPI Test,
                SCOPE ([Dim QPI Test].[Short Name].members);   
//use this calculation. 
                THIS = SUM([Dim QPI Grade].[Grade Name].[Grade Name], 
                            ([Measures].[Num Tests x Standard QPI]))/
                        SUM([Dim QPI Grade].[Grade Name].[Grade Name],
                            ([Measures].[Num Tests]));   

//If slicing only by Dim Mill, Dim QPI Test, and Dim QPI Grade,
                    SCOPE ([Dim QPI Grade].[Grade Name].members);   
//use this calculation.                    
                    THIS = //AVG([Dim QPI Grade].[Grade Name].currentmember,
                                (SUM([Dim QPI Grade].[Grade Name].currentmember, 
                                    ([Measures].[Num Tests x Standard QPI]))/
                                SUM([Dim QPI Grade].[Grade Name].currentmember,
                                    ([Measures].[Num Tests])));   

                    END SCOPE;   

                END SCOPE;   

            END SCOPE;   
            
//If slicing only by [Dim Mill].[Location],

            SCOPE ([Dim Mill].[Location].members);   

//get the average based on the Mill and Grade numbers.
            
            THIS = AVG([Dim Mill].[Mill].currentmember,
                    AVG([Dim QPI Grade].[Grade Name].currentmember,
                            (SUM([Dim Mill].[Location].currentmember, 
                                ([Measures].[Num Tests x Standard QPI]))/
                            SUM([Dim Mill].[Location].currentmember,
                                ([Measures].[Num Tests])))));   
            END SCOPE;   

        END SCOPE; 

This did take me a few days to work through, especially with me being the MDX noob that I am, so feel free to offer any criticisms. But at least the numbers I get back match the numbers returned by queries and calculations from the data warehouse.

Glad you got it working.  Those are some pretty complex overwrites.   You code looks fine to me.   If interested I blogged on a simple use of SCOPE here.  http://www.bidn.com/blogs/CurrentMember/bidn-blog/318/mdx-scripting-hiding-lower-levels-of-higher-grained-facts-like-monthly-forecast

Regards,

Luke

Thanks for your help, Luke.

I also did a little write up on a simple SCOPE statement: http://www.bidn.com/articles/mdx-and-dmx/94/mdx-scripting-with-scope-statement.