Pivot calculated fields, summaries, and values shown on chart issue

Good day everyone,

Pivot tables and slicers are wonderful for very fast viewing of different summaries and groups. I've noticed though that calculated fields produce different results when displaying simple averages vs weighted averages. For example, the table below results in two averages depending on how you produce the output. Pivot charts appear to operate with simple averages...

Month Date Wtd Met Count
Jan 1/1/2020 86 86 34
Jan 1/8/2020 83 83 67
Jan 1/15/2020 92 92 54
Jan 1/22/2020 76 76 98
Jan 1/29/2020 80 80 25
Feb 2/5/2020 83 83 45
Feb 2/12/2020 79 79 82
Feb 2/19/2020 87 87 55
Feb 2/26/2020 69 69 43
Mar 3/4/2020 77 77 49
Mar 3/11/2020 78 78 82
Mar 3/18/2020 96 96 86
Mar 3/25/2020 92 92 77
Apr 4/1/2020 84 84 69
Apr 4/8/2020 87 87 72
Apr 4/15/2020 81 81 74
Apr 4/22/2020 90 90 86
Apr 4/29/2020 84 84 99

Simple Ave of Wtd: 83.55
Weighted Ave of Wtd: 83.95

If I group Month with a slicer, the calculation also performs a simple average, without consideration to the Count of how many times a particular Met applied to Wtd. When charting the results in Month groups, it displays the simple average. Now, I can produce the true weighted average through formulas on the side, but I'm not sure if I can display the true weighted average on a chart.

So, the question is, is there a way to get the true weighted average on the chart? I may be "working around" the problem by adding the month field to make slicing easier. Would it change the calculation if I manipulated the "Grouping" at the pivot table level instead?

Input, thoughts, ideas, all appreciated.
Lewis

Hopefully the sample file attached...

Hi,
To calculate the weighted average, you must use the data model and DAX.
Regards

Thanks for the information stasinek. I looked it up, researched it a bit, found the value in this add in, and weeped softly, as the company I work for uses Office 2010. This wasn't introduced until 2013, and isn't available in my list of Com's. Nevertheless, it's a mental note for when the company upgrades. With 27,000 user licenses, I have no idea when that might be.

Back to the drawing board. At this point, I'm assuming I won't be able to have the pivot do the work, so I'm thinking I'll have to generate charts in VBA.

Thanks again for sharing the knowledge.
Lewis