Subtract subtotals in pivot table

[#1]

Hi all,

I've got a problem to subtract subtotal in a pivot table if a have dimension in rows and in columns. It is requirement often in finical P&L analysis.
As long as I have just 1 dimensions, the formula

sum({$<Parent={'1 Sales'}>} total (Value))

is working perfectly (Attached file complex Pivot /Pivot 2).

As soon as I have to add an additional dimension in column order, the "total" statement is calculated in each column by the overall subtotal by row instead of subtotal by row and column. (Attached file complex Pivot /Pivot 3).

Subtotals should be calculated as following. Since regular analysis needs to be done by several people, all using QV, a direct QV solution is much handier than an Export into Excel.

SOE	Parent	Period	2010 01	2010 02	Gesamt
1 Margin	1 Sales		-600	-1200	-1800
1 Margin	2 Standard COS		180	360	540
1 Margin	Subtotal (1 Margin)		-420	-840	-1260
2 Adj. Margin	3 Other COS		30	60	90
2 Adj. Margin	4 Variance		45	90	135
2 Adj. Margin	Subtotal (1 Margin - 2 Adj. Margin)		-345	-690	-1035
3 EBIT	5 G&A		60	120	180
3 EBIT	6 Selling		75	150	225
3 EBIT	7 Marketing		90	180	270
3 EBIT	8 Other Other above		105	210	315
3 EBIT	Subtotal (2 Adj. Margin - 3 EBIT)		-15	-30	-45
4 Net Income	9 Other below		10	20	30
4 Net Income	Subtotal (3 EBIT - 4 Net Income)		-5	-10	-15
Gesamt			-5	-10	-15

It would be great, if somebody could give me a hint how to handle this problem.

Many Thanks

Werner

[pover · Accepted solution]

Werner,

In order to define a total that respects the scope of a dimension use the following syntax:

sum(total <dimension> value)

The example is in the pivot table prueba in the attached file.

Pover

[#3]

Many Thanks ! It is working perfectly!