Different kinds of subtotals in Pivot Tables (Excel)

[mrmyr]

I would appreciate your support to solve a challenge with pivot tables...

I have the following table:

PROGRAM PROJECT TYPE VALUE
P1 Project 1.1 RISK 1 8
P1 Project 1.1 RISK 2 1,94
P1 Project 1.1 ISSUE 1 10,10
P1 Project 1.1 ISSUE 2 5
P1 Project 1.2 RISK 3 4
P1 Project 1.2 RISK 2 3,1
P1 Project 1.2 ISSUE 1 7,7
P1 Project 1.2 ISSUE 2 8,1
....

I expect that the pivot table to summarize this data by program showing the AVERAGE as follows:

PROGRAM RISK AVERAGE ISSUE AVERAGE
P1 4,26 7,72

Is it possible with a single pivot table?

Any idea how to implement it?

Thank you!

[JosephP]

the easiest way would be to add a new column to the source table returning either RISK or ISSUE for each row and use that as a column field

[mrmyr]

the easiest way would be to add a new column to the source table returning either RISK or ISSUE for each row and use that as a column field

Hi Joseph,

As always, thanks for your support!

I did what you suggested but still have no idea how to get it done.
I even tried to create the pivot table but I am stuck.

Your support would be appreciated!

Thanks

Sample.xlsx

[JosephP]

you cannot have a sum and an average in the same field. you would have to add the weighted value field to the data area again and change the summary function to average

[mrmyr]

I have implemented via VBA and it is now working fine.
Basically I went through all pivot table items and summed up their values and how many times they appear.
At the end divided both values and summarized them by program.