I have created a calculated measure, that I need the sum of in the grand total line, og when no dimension is selected. Until now, I have tried various methods, all of them ending in either wrong result or blank grand total.
The calculation is to select one measure over another. If the first is <> 0, choose that else if the other is <> 0 choose that, else end with NULL.
This is my current code, in a script:
SCOPE ([Measures].[Output Quantity 3]);
SCOPE ([Production Order No].[Production Order No].MEMBERS);
THIS =
IIF([Measures].[MC Output Quantity]<>0,
[Measures].[MC Output Quantity],
IIF([Measures].[PO Output Quantity]<>0,
[Measures].[PO Output Quantity],
NULL
)
);
END SCOPE;
END SCOPE;
Implemented after reading this article by Chriss Webb: https://blog.crossjoin.co.uk/2013/05/29/aggregating-the-result-of-an-mdx-calculation-using-scoped-assignments/
It does the trick on the line level, but repeats the same calculation/comparison on grand total.
Adding a level in the second SCOPE, like this:
SCOPE ([Measures].[Output Quantity 3]);
SCOPE ([Production Order No].[Production Order No].**[Production Order No]**.MEMBERS);
THIS =
IIF([Measures].[MC Output Quantity]<>0,
[Measures].[MC Output Quantity],
IIF([Measures].[PO Output Quantity]<>0,
[Measures].[PO Output Quantity],
NULL
)
);
END SCOPE;
END SCOPE;
Results in a blank grand total.
Other solutions have used calculation in measures instead of scope. I do not mind which one solves the problem.
I believe the issue is the same as described in this question: Simple summation of a column that has its own formula in mdx Just that I do not have hierarchies in my dimension(s). Also, I did not fully understand the answer "Try to make a query with different statement. Separating the conditions."
