{"id":12600,"date":"2026-05-19T09:40:19","date_gmt":"2026-05-19T15:40:19","guid":{"rendered":"https:\/\/www.mrc-productivity.com\/techblog\/?post_type=ht_kb&#038;p=12600"},"modified":"2026-05-19T09:40:20","modified_gmt":"2026-05-19T15:40:20","slug":"averaging-values-in-pivot-tables","status":"publish","type":"ht_kb","link":"https:\/\/www.mrc-productivity.com\/techblog\/?ht_kb=averaging-values-in-pivot-tables","title":{"rendered":"Averaging Values in Pivot Tables"},"content":{"rendered":"\n<p>The Pivot Table Report template aggregates data across various combinations of dimension keys. While standard summing rolls up perfectly across subtotal levels, standard averaging does not &#8212; defaulting to mathematically incorrect &#8220;averages of averages&#8221; across stacked dimensions. To ensure accurate reporting, this template uses a true weighted average. Setting this up requires two steps: setting the average aggregate in the app specifications and creating a Counter calculation.<\/p>\n\n\n\n<div class=\"wp-block-group\"><div class=\"wp-block-group__inner-container is-layout-constrained wp-block-group-is-layout-constrained\">\n<h3 class=\"wp-block-heading\" id=\"avg\">1. Set Aggregate Value to Average<\/h3>\n\n\n\n<p>First, you will need to manually set the aggregation type in the application&#8217;s JSON file.<\/p>\n\n\n\n<ol start=\"1\" class=\"wp-block-list\">\n<li>Open <strong>m-Painter<\/strong> for your Pivot Report.<\/li>\n\n\n\n<li>Click the down arrow next to the <strong>Save<\/strong> button and select <strong>Open File<\/strong>.<\/li>\n\n\n\n<li>Switch to the <strong>Other Files<\/strong> tab and select <strong>Application Specifications<\/strong>.<br><br><figure class=\"wp-block-image size-large\"><img decoding=\"async\" src=\"\/techblog\/images\/pivot_averages\/jsonFileSelection.png\"><\/figure><\/li>\n\n\n\n<li>Use the keyboard shortcut <strong>Ctrl+F<\/strong> to find the field name you wish to average.<\/li>\n\n\n\n<li>Once you locate the field block, find the nested property for <code>\"aggregate\"<\/code> and change its value to <code>\"avg\"<\/code>.<br>\n<figure class=\"wp-block-image size-large\"><img decoding=\"async\" src=\"\/techblog\/images\/pivot_averages\/jsonAggregate.png\" alt=\"\"><\/figure>\n<\/li>\n\n\n\n<li>Repeat as necessary for any additional fields needing to be averaged.<\/li>\n\n\n\n<li><strong>Save<\/strong> and close the JSON editor.<\/li>\n<\/ol>\n\n\n\n<p class=\"wp-block-ht-blocks-messages wp-block-hb-message wp-block-hb-message--withicon\">If you load the Field Settings page after this change, you will now see &#8220;Average&#8221; selected in the aggregate dropdown.<\/p>\n<\/div><\/div>\n\n\n\n<div class=\"wp-block-group\"><div class=\"wp-block-group__inner-container is-layout-constrained wp-block-group-is-layout-constrained\">\n<div class=\"wp-block-group\"><div class=\"wp-block-group__inner-container is-layout-constrained wp-block-group-is-layout-constrained\">\n<h3 class=\"wp-block-heading\">2: Add the Counter Calc<\/h3>\n\n\n\n<p>Because the pivot table template requires a weighted average, you must include a &#8220;Counter Calc&#8221; alongside your averaged numeric field.<\/p>\n\n\n\n<ol start=\"1\" class=\"wp-block-list\">\n<li>Create a new <strong>Database<\/strong> type <strong>Numeric<\/strong> calculation.<\/li>\n\n\n\n<li>Set the calculation description to exactly <strong>Count<\/strong> or <strong>Counter<\/strong>.<\/li>\n\n\n\n<li>Type <code>1<\/code> into the expression box and save.<\/li>\n<\/ol>\n\n\n\n<figure data-wp-context=\"{&quot;imageId&quot;:&quot;6a0dd2125d0da&quot;}\" data-wp-interactive=\"core\/image\" data-wp-key=\"6a0dd2125d0da\" class=\"wp-block-image size-large wp-lightbox-container\"><img decoding=\"async\" data-wp-class--hide=\"state.isContentHidden\" data-wp-class--show=\"state.isContentVisible\" data-wp-init=\"callbacks.setButtonStyles\" data-wp-on--click=\"actions.showLightbox\" data-wp-on--load=\"callbacks.setButtonStyles\" data-wp-on-window--resize=\"callbacks.setButtonStyles\" src=\"\/techblog\/images\/pivot_averages\/counterCalc.png\" alt=\"\"\/><button\n\t\t\tclass=\"lightbox-trigger\"\n\t\t\ttype=\"button\"\n\t\t\taria-haspopup=\"dialog\"\n\t\t\taria-label=\"Enlarge\"\n\t\t\tdata-wp-init=\"callbacks.initTriggerButton\"\n\t\t\tdata-wp-on--click=\"actions.showLightbox\"\n\t\t\tdata-wp-style--right=\"state.imageButtonRight\"\n\t\t\tdata-wp-style--top=\"state.imageButtonTop\"\n\t\t>\n\t\t\t<svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"12\" height=\"12\" fill=\"none\" viewBox=\"0 0 12 12\">\n\t\t\t\t<path fill=\"#fff\" d=\"M2 0a2 2 0 0 0-2 2v2h1.5V2a.5.5 0 0 1 .5-.5h2V0H2Zm2 10.5H2a.5.5 0 0 1-.5-.5V8H0v2a2 2 0 0 0 2 2h2v-1.5ZM8 12v-1.5h2a.5.5 0 0 0 .5-.5V8H12v2a2 2 0 0 1-2 2H8Zm2-12a2 2 0 0 1 2 2v2h-1.5V2a.5.5 0 0 0-.5-.5H8V0h2Z\" \/>\n\t\t\t<\/svg>\n\t\t<\/button><\/figure>\n\n\n\n<p class=\"wp-block-ht-blocks-messages wp-block-hb-message wp-block-hb-message--withicon is-style-alert\">If you do not wish to display this counter calc on the final output, you can easily hide the field from the Field Settings menu.<\/p>\n<\/div><\/div>\n\n\n\n<h4 class=\"wp-block-heading\">Missing Counter Calc Warning<\/h4>\n\n\n\n<p>The template contains a built-in safeguard to prevent mathematical errors. If you have a field set to average but the application does not include a counter calc, the report data will not run. Instead, you will see the following warning message:<\/p>\n\n\n\n<blockquote class=\"wp-block-quote is-layout-flow wp-block-quote-is-layout-flow\">\n<p><em>&#8220;A count field must be configured via calculations in order to provide averaged values.&#8221;<\/em><\/p>\n<\/blockquote>\n\n\n\n<figure data-wp-context=\"{&quot;imageId&quot;:&quot;6a0dd2125d860&quot;}\" data-wp-interactive=\"core\/image\" data-wp-key=\"6a0dd2125d860\" class=\"wp-block-image size-large wp-lightbox-container\"><img decoding=\"async\" data-wp-class--hide=\"state.isContentHidden\" data-wp-class--show=\"state.isContentVisible\" data-wp-init=\"callbacks.setButtonStyles\" data-wp-on--click=\"actions.showLightbox\" data-wp-on--load=\"callbacks.setButtonStyles\" data-wp-on-window--resize=\"callbacks.setButtonStyles\" src=\"\/techblog\/images\/pivot_averages\/runtimeMessage.png\" alt=\"\"\/><button\n\t\t\tclass=\"lightbox-trigger\"\n\t\t\ttype=\"button\"\n\t\t\taria-haspopup=\"dialog\"\n\t\t\taria-label=\"Enlarge\"\n\t\t\tdata-wp-init=\"callbacks.initTriggerButton\"\n\t\t\tdata-wp-on--click=\"actions.showLightbox\"\n\t\t\tdata-wp-style--right=\"state.imageButtonRight\"\n\t\t\tdata-wp-style--top=\"state.imageButtonTop\"\n\t\t>\n\t\t\t<svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"12\" height=\"12\" fill=\"none\" viewBox=\"0 0 12 12\">\n\t\t\t\t<path fill=\"#fff\" d=\"M2 0a2 2 0 0 0-2 2v2h1.5V2a.5.5 0 0 1 .5-.5h2V0H2Zm2 10.5H2a.5.5 0 0 1-.5-.5V8H0v2a2 2 0 0 0 2 2h2v-1.5ZM8 12v-1.5h2a.5.5 0 0 0 .5-.5V8H12v2a2 2 0 0 1-2 2H8Zm2-12a2 2 0 0 1 2 2v2h-1.5V2a.5.5 0 0 0-.5-.5H8V0h2Z\" \/>\n\t\t\t<\/svg>\n\t\t<\/button><\/figure>\n<\/div><\/div>\n","protected":false},"excerpt":{"rendered":"<p>The Pivot Table Report template aggregates data across various combinations of dimension keys. While standard summing rolls up perfectly across subtotal levels, standard averaging does not &#8212; defaulting to mathematically incorrect &#8220;averages of averages&#8221; across stacked dimensions. To ensure accurate reporting, this template uses a true weighted average. Setting this&#8230;<\/p>\n","protected":false},"author":10,"comment_status":"closed","ping_status":"closed","template":"","format":"standard","meta":{"footnotes":""},"ht-kb-category":[153],"ht-kb-tag":[],"class_list":["post-12600","ht_kb","type-ht_kb","status-publish","format-standard","hentry","ht_kb_category-general"],"_links":{"self":[{"href":"https:\/\/www.mrc-productivity.com\/techblog\/index.php?rest_route=\/wp\/v2\/ht-kb\/12600","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.mrc-productivity.com\/techblog\/index.php?rest_route=\/wp\/v2\/ht-kb"}],"about":[{"href":"https:\/\/www.mrc-productivity.com\/techblog\/index.php?rest_route=\/wp\/v2\/types\/ht_kb"}],"author":[{"embeddable":true,"href":"https:\/\/www.mrc-productivity.com\/techblog\/index.php?rest_route=\/wp\/v2\/users\/10"}],"replies":[{"embeddable":true,"href":"https:\/\/www.mrc-productivity.com\/techblog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=12600"}],"version-history":[{"count":2,"href":"https:\/\/www.mrc-productivity.com\/techblog\/index.php?rest_route=\/wp\/v2\/ht-kb\/12600\/revisions"}],"predecessor-version":[{"id":12603,"href":"https:\/\/www.mrc-productivity.com\/techblog\/index.php?rest_route=\/wp\/v2\/ht-kb\/12600\/revisions\/12603"}],"wp:attachment":[{"href":"https:\/\/www.mrc-productivity.com\/techblog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=12600"}],"wp:term":[{"taxonomy":"ht_kb_category","embeddable":true,"href":"https:\/\/www.mrc-productivity.com\/techblog\/index.php?rest_route=%2Fwp%2Fv2%2Fht-kb-category&post=12600"},{"taxonomy":"ht_kb_tag","embeddable":true,"href":"https:\/\/www.mrc-productivity.com\/techblog\/index.php?rest_route=%2Fwp%2Fv2%2Fht-kb-tag&post=12600"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}