Adding Month Average to Pivot Table

[Mel123]

Hi,

sorry if this is a very simple question but I cannot figure it out.

I would like to a month average line to my pivot chart, but if I use the average function in excel, it will give me the average by each individual employee. how do I show a total month average?

I need it as a pivot chart as there is new monthly data so I'll be adding the timeline slicers.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Date (dd/mm/yyyy)
[/TD]
[TD]Employee
[/TD]
[TD]Total Amount
[/TD]
[TD]Month Average (excel)[/TD]
[TD]Month Average (expected)
[/TD]
[/TR]
[TR]
[TD]01/01/2017
[/TD]
[TD]A
[/TD]
[TD]100
[/TD]
[TD]100
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]02/01/2017
[/TD]
[TD]B
[/TD]
[TD]150
[/TD]
[TD]150
[/TD]
[TD]125 (Jan 17)
[/TD]
[/TR]
[TR]
[TD]02/02/2017
[/TD]
[TD]C
[/TD]
[TD]200
[/TD]
[TD]200
[/TD]
[TD]200 (Feb 17)
[/TD]
[/TR]
[TR]
[TD]01/03/2017
[/TD]
[TD]D
[/TD]
[TD]220
[/TD]
[TD]220
[/TD]
[TD]220 (Mar 17)
[/TD]
[/TR]
</tbody>[/TABLE]

Thanks in advance

 

[sandy666]

something like this?

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#FFFF00]PowerQuery[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]Date[/td][td=bgcolor:#70AD47]Employee[/td][td=bgcolor:#70AD47]Month Average[/td][td=bgcolor:#70AD47]Total Amount[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]January[/td][td=bgcolor:#E2EFDA]A, B[/td][td=bgcolor:#E2EFDA]

125​

[/td][td=bgcolor:#E2EFDA]

250​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]February[/td][td]C[/td][td]

200​

[/td][td]

200​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]March[/td][td=bgcolor:#E2EFDA]D[/td][td=bgcolor:#E2EFDA]

220​

[/td][td=bgcolor:#E2EFDA]

220​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#FFFF00]PivotTable[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Date (Month)[/td][td=bgcolor:#DDEBF7]Sum of Total Amount[/td][td=bgcolor:#DDEBF7]Month Average[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Jan[/td][td]

250​

[/td][td]

125​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Feb[/td][td]

200​

[/td][td]

200​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Mar[/td][td]

220​

[/td][td]

220​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Grand Total[/td][td=bgcolor:#DDEBF7]

670​

[/td][td=bgcolor:#DDEBF7]

167.5​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#FFFF00]Both[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Date[/td][td=bgcolor:#DDEBF7]Employee[/td][td=bgcolor:#DDEBF7] Month Average[/td][td=bgcolor:#DDEBF7] Total Amount[/td][/tr]

[tr=bgcolor:#FFFFFF][td]January[/td][td]A, B[/td][td]

125​

[/td][td]

250​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]February[/td][td]C[/td][td]

200​

[/td][td]

200​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]March[/td][td]D[/td][td]

220​

[/td][td]

220​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Grand Total[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]

545​

[/td][td=bgcolor:#DDEBF7]

670​

[/td][/tr]
[/table]

 

[Mel123]

I can't post attachments, but I basically need to display the data by employee each month, then with a month average line to show which employees are above or below this month average line.

Can this be done with pivot chart? If I manually create a table as below, I can get the desired output, but I can't figure it out with pivot table.

Manual Table

Employee Name
Average Month Spend
Employee A
Employee B
Employee C

 

[sandy666]

I can't post attachments

You can post a link to shared excel file (onedrive, dropbox, googledrive or any similar)

 

[Mel123]

oh I see, here is the link to the file.

First tab is what I need the data to show, second tab is the data that I am stuck with : https://drive.google.com/open?id=1ihLn9MfN5Dv5iiEbn3zSxhSMQma5cnSf

Thank you.

 

[sandy666]

something like this?

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]Employee Name[/td][td=bgcolor:#70AD47]January[/td][td=bgcolor:#70AD47]February[/td][td=bgcolor:#70AD47]March[/td][td=bgcolor:#70AD47]April[/td][td=bgcolor:#70AD47]May[/td][td=bgcolor:#70AD47]June[/td][td=bgcolor:#70AD47]July[/td][td=bgcolor:#70AD47]August[/td][td=bgcolor:#70AD47]September[/td][td=bgcolor:#70AD47]October[/td][td=bgcolor:#70AD47]November[/td][td=bgcolor:#70AD47]December[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Avg[/td][td=bgcolor:#E2EFDA]

486.2833333​

[/td][td=bgcolor:#E2EFDA]

301.02​

[/td][td=bgcolor:#E2EFDA]

298.7766667​

[/td][td=bgcolor:#E2EFDA]

363.14​

[/td][td=bgcolor:#E2EFDA]

313.0333333​

[/td][td=bgcolor:#E2EFDA]

330.2766667​

[/td][td=bgcolor:#E2EFDA]

332.3533333​

[/td][td=bgcolor:#E2EFDA]

283.92​

[/td][td=bgcolor:#E2EFDA]

468.2766667​

[/td][td=bgcolor:#E2EFDA]

323.62​

[/td][td=bgcolor:#E2EFDA]

259.8133333​

[/td][td=bgcolor:#E2EFDA]

309.535​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Apple[/td][td]

511.5​

[/td][td]

404.14​

[/td][td]

413.02​

[/td][td]

353.75​

[/td][td]

343.54​

[/td][td]

492.57​

[/td][td]

324.09​

[/td][td]

347.81​

[/td][td]

220.23​

[/td][td]

194.8​

[/td][td]

192.23​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Banana[/td][td=bgcolor:#E2EFDA]

181.52​

[/td][td=bgcolor:#E2EFDA]

176.45​

[/td][td=bgcolor:#E2EFDA]

181.95​

[/td][td=bgcolor:#E2EFDA]

292.65​

[/td][td=bgcolor:#E2EFDA]

186.42​

[/td][td=bgcolor:#E2EFDA]

177.15​

[/td][td=bgcolor:#E2EFDA]

264.49​

[/td][td=bgcolor:#E2EFDA]

177.64​

[/td][td=bgcolor:#E2EFDA]

597.64​

[/td][td=bgcolor:#E2EFDA]

448.13​

[/td][td=bgcolor:#E2EFDA]

249.57​

[/td][td=bgcolor:#E2EFDA]

190.92​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Carrot[/td][td]

765.83​

[/td][td]

322.47​

[/td][td]

301.36​

[/td][td]

443.02​

[/td][td]

409.14​

[/td][td]

321.11​

[/td][td]

408.48​

[/td][td]

326.31​

[/td][td]

586.96​

[/td][td]

327.93​

[/td][td]

337.64​

[/td][td]

428.15​

[/td][/tr]
[/table]

this is not a PivotTable but PowerQuery only