Pivot Table Total - Correct until collapsed

[chris1987]

Hi,

I have a pivot table that has a function to calculate each line - which works Perfect when expanded but when the group is collapsed the value changes. I know why this is, it's because all of the values are rolled up on the subtotal row and then the logic is applied on to this new row.

e.g.

Expanded:

Customer	Item	2017 Sales	2018 Sales	Like for Like (Formula)	Like for Like (Value)
Cust1	12345	£1000	£2000	=if(not isnull([2017 Sales]) and not isnull([2018 Sales]),[2018 Sales],null())	£2000
	98765	-	£1000	=if(not isnull([2017 Sales]) and not isnull([2018 Sales]),[2018 Sales],null())
	54321	£500	£500	=if(not isnull([2017 Sales]) and not isnull([2018 Sales]),[2018 Sales],null())	£500
	99999	£200	-	=if(not isnull([2017 Sales]) and not isnull([2018 Sales]),[2018 Sales],null())

So you can see that when the group is expanded that in the "Like for Like Value" column the total is £2500, but when you collapse it you get the following:

Collapsed:

Customer	Item	2017 Sales	2018 Sales	Like for Like (Formula)	Like for Like (Value)
Cust1		£1700	£3500	=if(not isnull([2017 Sales]) and not isnull([2018 Sales]),[2018 Sales],null())	£3500

I need to find a way to keep the £2500 when the row is collapsed. I was thinking aggr() but I'm a bit unsure how to do this.

Any help would be appreciated

Cheers

Chris

[thevingo]

I guess, because your IF conditions work at Summary-level & not at detail/row-level.

That's why 2018 sales for item 98765 is also added to the Total

[thevingo]

try SET expression

[olivierrobin]

hello

maybe you couls try

sum(=if(not isnull([2017 Sales]) and not isnull([2018 Sales]),[2018 Sales],0)

[chris1987]

Thanks, I agree this is why it's happening but don't think SET expression will fix it? I need this to be dynamic as the user can select different period using filters etc.