Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
try SET expression
hello
maybe you couls try
sum(=if(not isnull([2017 Sales]) and not isnull([2018 Sales]),[2018 Sales],0)
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.