I have this situation:
Slot (dimension) Customer(Dimension) MinCounter(measure with MIN aggregation) MaxCounter(measure with MAX aggregation)
I'd like to create a calculated member that is a Delta of counters (MaxCounter - MinCounter) i used a simple formula:
[Measures].[MaxCounter] - [Measures].[MinCounter]
Obv it works when i use Slot dimension, because counters are relative to slot.
But I'd like to have the delta for every customer. Every customer have N slot It means that to calculate delta i need to calculate delta for every slot of a customer and SUM all of them to have the delta of the customer. So i tried:
(SUM(([Slot].[Code].children, [Measures].[MaxCounter])) - SUM(([Slot].[Code].children, [Measures].[MinCounter])))
But it doesn't work.
When i try to make a pivot with customer as dimension and Delta like measure, i obtain wrong result. Delta it seems calculated like :
max counter between ALL THE SLOT of the customer
min counter between ALL THE SLOT of the customer
It seems to be calculating that way because that is what you have asked it to so...
sum ( work out the max counter per slot ) - sum ( work out the min counter per slot )
when you want to ask
sum ( per slot; max counter - min counter)
try creating a calculated member like
with member [Measures].[CounterDiff] as
([Measures].[MaxCounter] - [Measures].[MinCounter])
and then using that in
sum([Slot].[Code].children, [Measures].[CounterDiff])