Subtotal wrong in Pivot Table when using IF

[annabergendal]

When I add an if-statement to my expression the subtotal gets wrong... How can I correct this?

I have tried with distinct which I read somewhere that you could use, and also suppress zeros for dimensions, but it did not work, please advice!

My expression:

If

(

Lagernr = 'A01' or Lagernr = 'A02' or Lagernr = 'AR1' or Lagernr = 'AB1' or Lagernr = 'AW1' or Lagernr = 'A03',

Sum(distinct(Lagersaldo)),

Sum(distinct(Lagersaldo)) + sum(DISTINCT(Ordered))

)

[Anonymous · Accepted solution]

Try this (I cleaned your expression a little, and added sum(aggr()) around it):

sum(aggr(

if(

match(Lagernr,'A01','A02','AR1','AB1','AW1','A03'),

sum(Lagersaldo),

sum(Lagersaldo)+sum(Ordered)

)

,Lagernr))

[annabergendal]

Thanks! Together with all the rest dimennsions in the aggr funktion it works... Great