Weighted averages in pivot table

Question

Hello,I want to create a calculated field in my pivot table, to see the average price of sold products. for this, I need to use the formula: "SUM(price*quantity)/SUM(quantity)". This gives me the weighted average. However, it seems like I can only do

"(SUM(price) * SUM(quantity) )/SUM(quantity)". This doesn't return the correct value.  Is there any way I can use the first formula here, or any way I can overwrite the functionality of the calculated fields to create this functionality myself?Kind regards,

AngelinFaithSheeba PaulvannanRajadurai · Answer

Hi Louis,Currently, we don't have any build-in option to calculate
the weighted average. However, we have checked the possibilities to achieve
your requirement at sample level, but we regret to let you know that this
requirement cannot be achieved with more than two levels in the rows. Because
when we have more than two levels, except for the last level, rest of the
levels will be displayed as sub-totals, thus it would be complex to calculate
the weighted average there and it leads to performance constraint.However, for your convenience, we created a pivot
table sample with only two levels in the row axis. In this code example,
we created a new field in the load event called "ProductOfAantalAndTarief"
to get the product of “Aantal” and “Tarief”. The weighted average of
"Tarief" was then calculated using that newly created field in the
calculated field formula.Code example:

// Need to create a field with formula (Aantal * Tarief) and add that field to the data source.
      load(args: any): void {
          for (var i = 0; i < args.dataSourceSettings.dataSource.length; i++) {
              var Aantal = args.dataSourceSettings.dataSource[i].Aantal;
              var Tarief = args.dataSourceSettings.dataSource[i].Tarief;
              var productOfAantalAndTarief = Aantal * Tarief;
              args.dataSourceSettings.dataSource[i].ProductOfAantalAndTarief =
                productOfAantalAndTarief;
            }
      }
   this.dataSourceSettings = {
                // Create a calculated field for weighted average here.
                calculatedFieldSettings: [
                  {
                    name: 'Weighted Average',
                    formula: '"Sum(ProductOfAantalAndTarief)"/"Sum(Aantal)"',
                  },
                ],
          }

Meanwhile,
we have prepared a sample for your reference.Sample: https://stackblitz.com/edit/angular-cvtmv5-nntmpp?file=src%2Fapp.component.ts,src%2Fapp.component.htmlOutput screenshot:Regards,
Angelin Faith
Sheeba