Forum Discussion
Compute weighed average by month
Hello,
I'm trying to find the way to do the following problem on PBI (I'm still pretty new). Let's say that I have the following table:
| ID | Quantity | Price | Month | Year |
| 1 | 50 | 7.5 | 1 | 2023 |
| 2 | 20 | 2.5 | 3 | 2023 |
| 1 | 10 | 7.0 | 1 | 2023 |
| 3 | 50 | 30.0 | 2 | 2023 |
| 1 | 5 | 7.6 | 1 | 2023 |
| 1 | 10 | 7.7 | 2 | 2023 |
| 3 | 15 | 20.0 | 2 | 2023 |
| 2 | 10 | 3.0 | 2 | 2023 |
| 2 | 1 | 5.0 | 2 | 2023 |
This table will represent the price of sold items (1, 2, 3) in differents months taking into account the quantities and unitary prices. I would like to compute the weighted average of each one of the items by month.
I created a table with disticnt elements but I don't know how to calculate this weighted average...
Hi, capko
You can try the following methods.
Measure = Var _N1=MAX('Table'[Price])*MAX('Table'[Quantity]) Var _N2=CALCULATE(SUM('Table'[Quantity]),ALLEXCEPT('Table','Table'[ID],'Table'[Year],'Table'[Month])) Return DIVIDE(_N1,_N2)Is this the result you expect?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
capko There is a weighted average quick measure or you may find this helpful as well: Better Weighted Average per Category - Microsoft Fabric Community
Hi, capko
You can try the following methods.
Measure = Var _N1=MAX('Table'[Price])*MAX('Table'[Quantity]) Var _N2=CALCULATE(SUM('Table'[Quantity]),ALLEXCEPT('Table','Table'[ID],'Table'[Year],'Table'[Month])) Return DIVIDE(_N1,_N2)Is this the result you expect?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
