Table:
X Y wins losses
Apples Oranges 5 2
Apples Limes 2 1
Apples Strawberries 3 3
Limes Pears 5 4
Limes Strawberries 6 8
Strawberries Limes 2 3
Strawberries Apples 1 5
I need to sum [X].Apples wins where Y = Strawberries and divide that by [X].Strawberries total losses. [Measures].[some_calculated_member] = 3 / (5 + 3)
An alternative would be to calculate it this way: SUM([X].Apples wins where Y = Strawberries / SUM([Y].Strawberries wins)
I'm not sure how to structure the MDX query to accomplish what I need:
SELECT
{ [Measures].[some_calculated_member] } ON 0,
{ [X].[Apples] } ON 1,
{ [X].[Strawberries] } ON 2,
{ [Y].[Strawberries] } ON 3
FROM [cube]
I've looked into using AXIS
but I'm using Mondrian, which doesn't support AXIS
.
Did you try with a calculated measure?
WITH
Member [Measures].[Ratio] AS (
[Measures].[wins.SUM],
[X].[ALL].[AllMember].[Apples],
[Y].[ALL].[AllMember].[Strawberries]
) / (
[Measures].[losses.SUM],
[Y].[ALL].[AllMember].[Strawberries]
)
SELECT NON EMPTY {
[Measures].[New Measure]
} ON COLUMNS
FROM [SomeCube]