Not really sure of the proper terminology that I am asking for, so bear with me. I have an MDX cube. One of the measures in the cube is called [Duration]. I have a dimension called Commands and another dimension called Locations. Basically, the duration measure tells me how long it took to run a specific command...aggregated across a bunch of dimensions.
I need to be able to compare the [duration] measure of all Locations against one specific location (Location 1). Location 1 is basically the best case scenario. The [duration] at all other locations should almost never be better than the duration of Location 1...but the question I need to answer is how much worse is location 2, 3, 4, etc. compared to location 1.
I need it to be an apples to apples comparison, so if the user brings in one of the many other dimensions that are available, I need this comparison to properly reflect all the slices against Location 1 and the current location. Does anyone have any ideas? I can do pretty much whatever I want to make this work, so any useful ideas are welcome.
Thanks
If you need to check the duration measure against one location, you may use the following calculated measure:
[Measures].[Duration] / ([Location].[Location].&[Reference location],[Measures].[Duration])