Forum Discussion
Calculate the average per category
Hi,
i was searching yesterday within the community for a solution but did not find anything that matches my case. If this was already discussed i would be glad if somebody can piont out the post.
I would like to calculate the average price for different categories. The difficulity is that i have rows with different quantities. Here an example
| line | Prodcut Type | qty | Total price |
| Platform 1 | A | 15 | € 61.51 |
| Production line | Product name | qty | total price (not price per piece) |
I don't know how i generate the correct calculation. I want to calculate the sum of total price and devide this by the sum of qty.
Here is an excerpt of the table
| line | Prodcut Type | qty | Price | Price per piece |
| Platform 1 | A | 15 | € 61.51 | € 4.10 |
| Platform 1 | B | 14 | € 60.32 | € 4.31 |
| Platform 1 | G | 21 | € 60.32 | € 2.87 |
| Platform 1 | G | 42 | € 58.47 | € 1.39 |
| Platform 1 | J | 33 | € 58.47 | € 1.77 |
| Platform 1 | W | 49 | € 139.00 | € 2.84 |
| Platform 2 | J | 15 | € 281.88 | € 18.79 |
| Platform 2 | Q | 27 | € 143.39 | € 5.31 |
| Platform 2 | R | 13 | € 57.04 | € 4.39 |
| Platform 2 | H | 8 | € 57.04 | € 7.13 |
| Platform 2 | F | 12 | € 93.04 | € 7.75 |
| Platform 2 | B | 20 | € 206.89 | € 10.34 |
| Platform 2 | B | 35 | € 207.36 | € 5.92 |
| Platform 2 | C | 25 | € 126.34 | € 5.05 |
| Platform 2 | R | 24 | € 126.34 | € 5.26 |
| Platform 3 | T | 39 | € 131.94 | € 3.38 |
| Platform 3 | O | 2 | € 138.16 | € 69.08 |
| Platform 3 | P | 47 | € 56.24 | € 1.20 |
| Platform 3 | Y | 44 | € 64.48 | € 1.47 |
| Platform 3 | W | 43 | € 253.28 | € 5.89 |
| Platform 3 | R | 20 | € 282.25 | € 14.11 |
| Platform 3 | D | 27 | € 210.94 | € 7.81 |
These are the average values that i would like to calculate:
| Line | Total Qty | Total Price | Average |
| Platform 1 | 174 | € 438.09 | € 2.52 |
| Platform 2 | 179 | € 1,299.32 | € 7.26 |
| Platform 3 | 222 | € 1,137.29 | € 5.12 |
I am sure that there is a pretty easy solution which i did not consider.
Thanks for you help!
Which calculations you have used.
In case you have used this, hope you created it is a measure, not Column
divide(sum(price),sum(qty))Please share the formula you created. It should be dynamic. Unless there is join missing. Or Interactions are off.
5 Replies
- amitchandak
Super User
Try like
divide(sum(price),sum(qty))This will adjust according to the group by.
In case you need a category level fixed. try
https://community.powerbi.com/t5/Desktop/Calculate-Average-per-category/td-p/362637
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners/ba-p/890814
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p/881739
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601Thanks for you answer.
Using the solution mentioned in the provided link i get the correct calculation. The problem now is that the calculation seems to be static.
I have data from several months. If i select now a single month in my report the calculation does not adjust.
Is it possible to create a dynamic calculation?
- amitchandak
Which calculations you have used.
In case you have used this, hope you created it is a measure, not Column
divide(sum(price),sum(qty))Please share the formula you created. It should be dynamic. Unless there is join missing. Or Interactions are off.