MDX is not my strong point, so I'm hoping someone can help me out here as there isn't an example in the Foodmart database.
We have a cube that has a measure called 'Unit Price'. Obviously, that is only valid at the atomic level. As soon as we start to aggregate it becomes meaningless if we summate it. The users, in their infinite wisdom, have decided to average this measure as we move up through the cube (although I don't see what benefit even that has - but my role is to serve so I have to do it).
So, my question is, what is the MDX to calculate the average of a measure as we role up through the cube? It needs to be available for all dimensions and intersects. I tried using the AVG function and double clicked the appropriate place holders for Set, Dimension etc but I ended up with another Dimension place holder so was going round in circles. I suspect it is something simple, like using a CurrentDimension.CurrentMember sort of expression, but I can't figure it out.
Hopefully, someone out there knows if this is possible, and how to do it.
Thanks in advance.
40 million lemmings can't be wrong........can they?
We have a cube that has a measure called 'Unit Price'. Obviously, that is only valid at the atomic level. As soon as we start to aggregate it becomes meaningless if we summate it. The users, in their infinite wisdom, have decided to average this measure as we move up through the cube (although I don't see what benefit even that has - but my role is to serve so I have to do it).
So, my question is, what is the MDX to calculate the average of a measure as we role up through the cube? It needs to be available for all dimensions and intersects. I tried using the AVG function and double clicked the appropriate place holders for Set, Dimension etc but I ended up with another Dimension place holder so was going round in circles. I suspect it is something simple, like using a CurrentDimension.CurrentMember sort of expression, but I can't figure it out.
Hopefully, someone out there knows if this is possible, and how to do it.
Thanks in advance.
40 million lemmings can't be wrong........can they?