MDX Sum of Min on a set without using the set in the select

0
votes

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

1
ssasmdx

1 Answers

0
votes

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])