Hi all,
I've seen the "variance in a pivot" question asked around here a fair bit and and happy that this is fairly easy using a calculated item.
My issue seems a little more complex though.
Basically, i'm trying to create the following in a pivot.
| | | Debit/Credit | Debit | Debit | Debit | Credit | Credit | Credit | Variance |
|---|
| | Product | P1 | P2 | P3 | P1 | P2 | P3 | Variance |
| Branch | Region | Name | Amount | Amount | Amount | Amount | Amount | Amount | Amount |
| 1111 | 1 | TestBranch | 30 | 5 | 5 | 50 | 3 | 20 | 33 |
| 2222 | 2 | TestBranch2 | 10 | 20 | 30 | 1 | 5 | 10 | -34 |
I can't seem to work out how to get the variance column to be the total debits subtract the total credits but only have the single column at the end.
I've tried adding a union with a "variance" value in the debit/credit and product columns but it seems to act strangely.
I was hoping there would be an easier way that I wasn't aware of.
Thanks in advance,
Adam