Analysis Services - Calculations Based on Dimension Value
Posted on 2007-11-15
I've trying to create a calculation based on a dimension. Basically, I have a "Type of Goal" which is text that could display like the following:
My dimensions involve time, geographic location, etc...My measures involve dealing with SUMs and COUNTs based on Pricing and Counts of Records. I'm looking to create a calculation that calculates differently based on the Type of Goal as listed above with 4 examples. Taking "Sales Goal" for example, this would change to calculate the number of records based on certain variables such as the Sale Date falling within the Time frame being reported on within the CUBE (i.e.: Quarter, Year, Month, etc...). The "Sales Volume" for example, this would change to calculate the sum of the Sales Price based on the status of the deal being Sold with the Sale Date falling within the Time frame being reported on within the CUBE (i.e.: Quarter, Year, Month, etc...).
Is it possible to setup a calculation to change based on the "Type of Goal"? If so, could anyone provide an example of how to do this? Once I get the grasp of setting this up then we'll be good to go. I'm somewhat new to calculations within CUBEs so be gentle, :P.
Anyways, any ideas/examples on this would be fantastic!