Show number of rows pr element in a pivottabel

[Peter]

Hi.

In Excel 2007 I have a pivottabel showing

A C
Projecttype Turnover
Maintenance 100
Repair 50

In Column B I have (and collapsed) the projects behind the Turnover :

A B C
Projecttype Project Turnover
Maintenance Proj 1 5
Proj 7 95
Repair Proj 5 7
Proj 10 10
Proj 12 33

I would like to show the number of project (2 Maintenace and 3 Repair)
in ny Privottable. Either in a new column or somehow else E.g.:

A C D
Projecttype Number Turnover
Maintenance 2 100
Repair 3 50

Of course I would like to use the Number information to calculate the
average turnover, ...

CAN I DO THIS ?

Regards

Peter

 

[Roger Govier]

Hi Peter

You can show Average Turnover, without showing count if you wish.
Just drag Turnover tot the data area a second time, click on the field
heading in the Values area>Value Field Settings>Average
If you do want to show the Count as well, then drag Project to the Values
area. It will automatically default to Count, as the field contains text
entries
So in your Values area you should have
Count of Project
Sum of Turnover
Average of Turnover

 

[Peter]

God aften Roger.

Thank you for the input. Unfortunately it dosn't work in my case !

The actual pivottable is build on data from the erp-system, and
"below" the project I have all the postings (costs, hours, invoices
etc) and budgets for the projects. If I follow your advice, the number
of projects will be the number of elements in the hiearacy of Project-
Posting/Budget.

The data are a view, and it could be, that one of the answers is, that
I should have used a cube instead - but I didn't.

Regards

Peter

 

[Peter]

Hi Peter

You can show Average Turnover, without showing count if you wish.
Just drag Turnover tot the data area a second time, click on the field
heading in the Values area>Value Field Settings>Average
If you do want to show the Count as well, then drag Project to the Values
area. It will automatically default to Count, as the field contains text
entries
So in your Values area you should have
Count of Project
Sum of Turnover
Average of Turnover
--
Regards
Roger Govier














- Vis tekst i anførselstegn -

God aften Roger.

Thank you for the input. Unfortunately it dosn't work in my case !


The actual pivottable is build on data from the erp-system, and
"below" the project I have all the postings (costs, hours, invoices
etc) and budgets for the projects. If I follow your advice, the
number
of projects will be the number of elements in the hiearacy of
Project-
Posting/Budget.


The data are a view, and it could be, that one of the answers is,
that
I should have used a cube instead - but I didn't.


Regards


Peter