P
Peter
Hi.
(Excel 2010)
I am quite sure, that the answer is no, but I try to ask:
Would it be possible to have subtotals in a pivottable showing all the
items in the next level instead of "only" one total.
I have a talbe with groups and product sold in different months. Each
sales have a cost and sales minus cost is earned. I have calculated
the ration in the pivottable as iserror(earned/sales;0).
My challenge is that I would like to se not only the sales, cost,
earned and ration for each product (a,b,c and d in my excample) but
also for each of the groups (transport and trace) and for the total.
I have solved the problem with 3 copies of the same pivottalbe,
showing different level of specification. But isn't it overkill ?
Regards
Peter
Data
Group Product Month Type Amount
transport a 01-01-2010 Sales 10
transport a 01-01-2010 Cost 4
transport a 01-01-2010 Earned 6
transport b 01-01-2010 Sales 15
transport b 01-01-2010 Cost 7
transport b 01-01-2010 Earned 8
Trace c 01-01-2010 Sales 7
Trace c 01-01-2010 Cost 1
Trace c 01-01-2010 Earned 6
Trace d 01-01-2010 Sales 14
Trace d 01-01-2010 Cost 20
Trace d 01-01-2010 Earned -6
transport a 01-02-2010 Sales 5
transport a 01-02-2010 Cost 4
transport a 01-02-2010 Earned 1
transport b 01-02-2010 Sales 9
transport b 01-02-2010 Cost 2
transport b 01-02-2010 Earned 7
Trace c 01-02-2010 Sales 12
Trace c 01-02-2010 Cost 10
Trace c 01-02-2010 Earned 2
Trace d 01-02-2010 Sales 14
Trace d 01-02-2010 Cost 20
Trace d 01-02-2010 Earned -6
Pivottable
Sum af Amount
j-10 f-10 Grandtotal
transport a Sales 10 5 15
Cost 4 4 8
Earned 6 1 7
Ratio 0,6 0,2 0,8
b Sales 15 9 24
Cost 7 2 9
Earned 8 7 15
Ratio 0,533333333 0,777777778 1,311111111
Trace c Sales 7 12 19
Cost 1 10 11
Earned 6 2 8
Ratio 0,857142857 0,166666667 1,023809524
d Sales 14 14 28
Cost 20 20 40
Earned -6 -6 -12
Ratio -0,428571429 -0,428571429 -0,857142857
(Excel 2010)
I am quite sure, that the answer is no, but I try to ask:
Would it be possible to have subtotals in a pivottable showing all the
items in the next level instead of "only" one total.
I have a talbe with groups and product sold in different months. Each
sales have a cost and sales minus cost is earned. I have calculated
the ration in the pivottable as iserror(earned/sales;0).
My challenge is that I would like to se not only the sales, cost,
earned and ration for each product (a,b,c and d in my excample) but
also for each of the groups (transport and trace) and for the total.
I have solved the problem with 3 copies of the same pivottalbe,
showing different level of specification. But isn't it overkill ?
Regards
Peter
Data
Group Product Month Type Amount
transport a 01-01-2010 Sales 10
transport a 01-01-2010 Cost 4
transport a 01-01-2010 Earned 6
transport b 01-01-2010 Sales 15
transport b 01-01-2010 Cost 7
transport b 01-01-2010 Earned 8
Trace c 01-01-2010 Sales 7
Trace c 01-01-2010 Cost 1
Trace c 01-01-2010 Earned 6
Trace d 01-01-2010 Sales 14
Trace d 01-01-2010 Cost 20
Trace d 01-01-2010 Earned -6
transport a 01-02-2010 Sales 5
transport a 01-02-2010 Cost 4
transport a 01-02-2010 Earned 1
transport b 01-02-2010 Sales 9
transport b 01-02-2010 Cost 2
transport b 01-02-2010 Earned 7
Trace c 01-02-2010 Sales 12
Trace c 01-02-2010 Cost 10
Trace c 01-02-2010 Earned 2
Trace d 01-02-2010 Sales 14
Trace d 01-02-2010 Cost 20
Trace d 01-02-2010 Earned -6
Pivottable
Sum af Amount
j-10 f-10 Grandtotal
transport a Sales 10 5 15
Cost 4 4 8
Earned 6 1 7
Ratio 0,6 0,2 0,8
b Sales 15 9 24
Cost 7 2 9
Earned 8 7 15
Ratio 0,533333333 0,777777778 1,311111111
Trace c Sales 7 12 19
Cost 1 10 11
Earned 6 2 8
Ratio 0,857142857 0,166666667 1,023809524
d Sales 14 14 28
Cost 20 20 40
Earned -6 -6 -12
Ratio -0,428571429 -0,428571429 -0,857142857