I have a very simple IIF statement in my cube calculation to create a calculation called "Commissionable Units":
IIF([Measures].[Commission]>0, [Measures].[Unit Balance]*1, NULL)
At the lowest grain I get the correct result, but as soon as I aggregate at any level, I get incorrect results
Example:
transaction #, Unit Balance, Commission, Commissionable Units
1, 500, 3, 500
2, 100, 0, 0
3, 250, 1, 250
When I remove the transaction dimension I get:
850, 4, 850
I should get:
850, 4, 750
I should mention that commission and units are in two different fact tables as they are different grains.
