I am new to MDX and find it hard to search for someone with the same issue. The problem is how the grand total of the column 'Difference' is calculated. Currently this is the situation:
| Item | Current Sales | Forecast Sales | Difference |
|---|---|---|---|
| A | 200.000 | 150.000 | 50.000 |
| B | 100.000 | 110.000 | 10.000 |
| Tot | 300.000 | 260.000 | 40.000 (should be 60.000) |
The formula for Difference is the absolute (ABS) of 'Current Sales' - 'Forecast Sales' (so negative values will be changed to positive.
But this effects the grand total. The grand total should always be a SUM of the total of all differences per item, even when collapsed.
So in this situation, it looks like the difference between Forecast and Current sales is 0, but actually it should be 200.000:
| Item | Current Sales | Forecast Sales | Difference |
|---|---|---|---|
| A | 200.000 | 100.000 | 100.000 |
| B | 100.000 | 200.000 | 100.000 |
| Tot | 300.000 | 300.000 | 0 (should be 200.000) |
Is there a way in MDX to make this happen?
The actual current code in MDX:
CREATE MEMBER CURRENTCUBE.[Measures].[Difference] AS Abs (
[Measures].[Current Sales] - ([Measures].[Forecast Sales],
[Sales Forecast Filter].[Type].[2]))
The filter only makes sure the right values are added.