Intercompany Sales Variance table

[martyn_birzys]

I need to build a report on intercompany sales - a bit similar to contras, but not quite.

I have 3 companies tables and sales amount fields

                    

Company A

Sales to B

Sales to C

Company B

Sales to A

Sales to C

Company C

Sales to A

Sales to B

To display sales matrix I have no problem:

	A	B	C
A
B
C

But how to build a table to show variance in sales between companies? e.g. Company A sales to B vs Company B sales to A.

I can do it with a bunch of text boxes and this script:

Sum({< CompanyName={'Company A'}>}[Sales to B]) - Sum({< CompanyName={'Company B'}>}[Sales to A])

But how to build this into a table?

[MK_QSL]

You can do it by Script end... Provide some sample data to work..

[martyn_birzys]

I would have 6 text boxes with the following expressions:

Sum({< CompanyName={'Company A'}>}[Sales to B]) - Sum({< CompanyName={'Company B'}>}[Sales to A])

Sum({< CompanyName={'Company A'}>}[Sales to C]) - Sum({< CompanyName={'Company C'}>}[Sales to A])

Sum({< CompanyName={'Company B'}>}[Sales to A]) - Sum({< CompanyName={'Company A'}>}[Sales to B])

Sum({< CompanyName={'Company B'}>}[Sales to C]) - Sum({< CompanyName={'Company C'}>}[Sales to B])

Sum({< CompanyName={'Company C'}>}[Sales to A]) - Sum({< CompanyName={'Company A'}>}[Sales to C])

Sum({< CompanyName={'Company C'}>}[Sales to B]) - Sum({< CompanyName={'Company B'}>}[Sales to C])

In reality I have 8 companies, so I end up with sheet-full of text boxes which are time consuming to maintain.

I need to build a table where instead of Sales values I would have Variance values.