I have a problem which looked like a simple requirement ... it turned out it wasn't ... at least for me. At the moment I feel like I've read half of the MDX internet ... I'm using latest Saiku CE (Mondrian 4), and my simplified cube looks like this:
Dimensions:
- Machine.Manufacturer
Measures:
- Measure.[Msg count]
- Measure.[Distinct machines]
- Measure.[Distinct days]
Calculated measures:
Measure.[Msg xMxD] which is basically: (Measure.[Msg count] / Measure.[Distinct machines] / Measure.[Distinct days]).
Measure.[Msg xMxd %] which is: (Measure.[Msg xMxD] / SUM(Measure.[Msg xMxD], Machine.[Manufacturer].[All Manufacturers]))
What I want to accomplish is this table:
But as you've probably guessed, I have a problem with the Measure.[Msg xMxd %] measure ... Because it is calculated on base of another calculated measure, the % calculation is done after the summing for a particular Manufacturer and I don't know how to overcome this. The closest answer I found was this one: https://forums.pentaho.com/threads/160265-Calculate-members-in-mdx/ ... but this concerns only one generated member as a sum of all manufacturers.
I've found also some resolutions based on Axis(...) functions but those are unavailable in Mondrian.
Do you have any ideas ? Is there a possibility to generate a set of calculated members ? this would (at least theoretically) give me a possible to set solve order for all child members of [Machine].[Manufacturer]
Any help is much appreciated.