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]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Removed Columns" = Table.RemoveColumns(Source,{"Column1"}),
    #"Filled Down" = Table.FillDown(#"Removed Columns",{"Project Name"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Filled Down",{"Employee"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns1", "Sum", Splitter.SplitTextByDelimiter("", QuoteStyle.Csv), {"Sum.1", "Sum.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Sum.1", Int64.Type}, {"Sum.2", type text}}),
    #"Removed Columns2" = Table.RemoveColumns(#"Changed Type",{"Sum.2"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns2", {"Project Name"}, {{"Count", each Table.RowCount(Table.Distinct(_)), Int64.Type}, {"SumT", each List.Sum([Sum.1]), type nullable number}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Average", each [SumT]/[Count])
in
    #"Added Custom"

 

[Arts]

Thanks for this Kerry

How would I incorporate this into my file ? I see it says Power Query but I have never used this. if you could provide me steps or any insights via links that would be most appreciated as this seems to give me what I need.

 

[RoryA]

You can also do it in the pivot table using Power Pivot, but we'd need some sample data.

 

[Kerryx]

Is this what you wanted?

Book3
	E	F	G
1	Row Labels	Sum of Sum	Average of Sum2
2	Project A
3	Simon A	36	36
4	Simon B	62	62
5	Simon C	32	32
6	Simon D	55	55
7	Simon E	45	45
8	Project A Total	230	46
9	Project B
10	Sally A	55	55
11	Sally B	32	32
12	Project B Total	87	43.5
13	Project C
14	Tom A	42	42
15	Tom B	45	45
16	Tom C	56	56
17	Tom D	63	63
18	Project C Total	206	51.5
Sheet1

 

[Arts]

Hey Rory

Is the below enough ? (I've naturally simplified the data to hide project names/employees but essentially the data would be setup as below )

Employee	Project Name	Project ID	September	October	November	Cost Centre
Simon A	Project A	A-001	64​	36​	41​	BA001
Simon B	Project A	A-001	26​	62​	17​	BA001
Simon C	Project A	A-001	29​	32​	30​	BA001
Simon D	Project A	A-001	76​	55​	42​	BA001
Simon E	Project A	A-001	34​	45​	42​	BA001
Sally A	Project B	B-001	38​	55​	40​	BB001
Sally B	Project B	B-001	68​	32​	42​	BB001
Tom A	Project C	C-001	19​	42​	52​	BC001
Tom B	Project C	C-001	47​	45​	45​	BC001
Tom C	Project C	C-001	28​	56​	66​	BC001
Tom D	Project C	C-001	31​	63​	45​	BC001
Ali A	Project D	D-001	26​	24​	19​	BD001
Ali B	Project D	D-001	53​	18​	60​	BD001
Ali C	Project D	D-001	25​	32​	48​	BD001
Ali D	Project D	D-001	15​	42​	31​	BD001

 

[Arts]

@Kerry

Yes! Thank you that's what I'm after but when I do this with my results I seem to get a different set of averages. I'll try and post some non sensitive data to show as to what I'm getting

 

[Arts]

I have figured out why I am getting differing numbers when an averages are applied, Project 3 should show an average of 105 but the first entry Mike A has two entries assigned to that project. One is 126 and the other is 0. This is seen as two entries so is taking the average of him individually and not the average of the number of employees assigned to that project from the pivot (if that makes sense). So in short my initial question can a pivot take the average of a number of employees assigned to that project within the results to get the average is leading me to think no.

PMU Project Name	Employee Name	Sum of April	Average of April
Project 1	Steve A	0	0
	Steve B	0	0
	Steve C	0	0
	Steve D	0	0
	Steve E	14	4.666666667
Project 1 Total		14	1.555555556
Project 2	Alan A	52.5	52.5
Project 2 Total		52.5	52.5
Project 3	Mike A	126	63
	Mike B	126	42
	Mike C	63	31.5
Project 3 Total		315	45

Thank you all that took the time out to look into this and respond.

 

[RoryA]

Hey Rory

Is the below enough ? (I've naturally simplified the data to hide project names/employees but essentially the data would be setup as below )

Ugh, I hate data like that. It's already partially summarised which makes it a pain to work with. I'd suggest using both Power Query (to unpivot the values columns into 2 columns - one for month and one for value), then load that into the data model so that you can create measures for the sum and averages. I'll post some samples when I have a bit more time.

 

[Arts]

Thanks Rory

But please don't put to much time into it, I've got a slight work around to work out the average using this formula (basically summing the Project Total's within the pivot) =IF(RIGHT(D26,5)="Total",IF(AND(D26<>""),AVERAGEIF($C$7:$C$1200,C25,$L$7:$L$1200),""),"")

Also as a side note I'm not particularly familiar with Power Query but will do a bit of a read up on it now.