weighted average of a data inside a measurment

[opittaluga]

Hello community, I hope somebody can help me with this calculation.

The result I’m expecting is to show the weighted average of the price variation.

I will show how can I do this on power pivot and how far I’m with power BI.

For this I will need:

1) The weighted average price by quarter.

2) The price variation between the vendor’s price and the average price by quarter.  

3) And finally calculate the weighted average of those variations.

The Table

Excel

• I calculated the average price and the total weight purchased per vendor per quarter by using a pivot table. To calculate the weighted average price.

• Then I calculated the price’ variation with a simple formula ((vendor price - average price) /average price.)

  

• Finally, I calculate the weighted average price variation using sum product formula.

 

Power BI

• I calculated the average price using the following formula:

Weighted Average Price = sum(Tabla1[Total])/sum(Tabla1[Amount (lb)])

• Then I obtained the price variation between the vendor’s price and the average price by using two formulas:

-Total weighted average = CALCULATE (SUM ( Tabla1[Total]) / SUM ( Tabla1[Amount(lb)] ); ALL ( Tabla1[Vendor ] ))

-Variation = ([Weighted Average Price]-[-Total weighted average])/[-Total weighted average]

• Here is where I got stack. I don’t know how to get the weighted average of those variations. At the beginning, I thought it was the total  of the table, but it is not. The total on the table is a very different number.

Any ideas to solve this enigma?

Best

[v-sihou-msft · Accepted solution]

@opittaluga

In this scenario, since you already put a measure into matrix, we can't define aggregation on a measure. And it's not possible to custom aggregation on Grand/Sub Total level.

For your requirement, it can't be achieved currently. Please vote a similar idea.

Regards,