Pivot Table Query/Confirmation

[Arts]

Hi all

I have a feeling the answer the is no but was wondering if the below was possible within a pivot table?

Would it be possible to get the average based on the number of entries based on a field.

So for "Project A" would I be able to attain the average of 46 as this has five entries under Employee and then average for Project B would be 43.5 as this has two employees assigned to it etc. I have tried various setting within the value field but to no avail so hoping I may have missed something in order to give me what I need.

Project Name	Employee	Sum	Desired Outcome
Project A	Simon A	36​	36​
	Simon B	62​	62​
	Simon C	32​	32​
	Simon D	55​	55​
	Simon E	45​	45​
Project A Total		230​	46​
Project B	Sally A	55​	55​
	Sally B	32​	32​
Project B Total		87​	43.5​
Project C	Tom A	42​	42​
	Tom B	45​	45​
	Tom C	56​	56​
	Tom D	63​	63​
Project C Total		206​	51.5​

Thanks as always!

 

[Kerryx]

Here are some good links from @alansidman on how to set up and use PQ

How to use Power Query

 

[RoryA]

It's well worth the time learning at least the basics of PQ - it's a real game changer and can save a lot of time.

 

[Arts]

Here are some good links from @alansidman on how to set up and use PQ

How to use Power Query

Thanks Kerry

It's well worth the time learning at least the basics of PQ - it's a real game changer and can save a lot of time.

Agree Rory I was more coming of it from an angle that if you did provide something with PQ it was the amount of annoyance you may have got from me asking you many questions around it. But I'll look into the basics of it starting with what Kerry sent

Thanks both!

 

[RoryA]

For this example, the PQ would be fairly simple - just select all the non month columns, then choose Unpivot other columns.

 

[Arts]

Works tasks pending today's lunch hour is potentially booked up with PQ tutorials, this could be a good one to start with if it's as easy as you mention. Ideally I will need a better solution then my formula fix as this will run into issues when the pivot table is updated with new data each month.