Forum Discussion
Weighted AVG Aggregation
Hi,
I got a problem when creating a calculated member in PowerBI.
Here is the sample data:
| Category | Product | Sold | Total |
| 1 | 1 | 123 | 4444 |
| 1 | 2 | 0 | 0 |
| 1 | 3 | 3 | 66 |
| 2 | 4 | 1 | 2 |
| 2 | 5 | 5 | 5 |
| 2 | 6 | 10 | 12 |
| 3 | 7 | 999 | 2000 |
| 3 | 8 | 50 | 50 |
| 3 | 9 | 12 | 22 |
I want to have a Sold Rate as a calculated column.
If I create a calculated column in Excel pivot table, it would be: IF(Sold = 0 , 0 , Sold/Total)
It works perfect as below:
| Values | ||||
| Category | Product | Sum of Sold | Sum of Total | Sum of Sold Rate |
| 1 | 1 | 123 | 4444 | 2.8% |
| 2 | 0 | 0 | 0.0% | |
| 3 | 3 | 66 | 4.5% | |
| 1 Total | 126 | 4510 | 2.8% | |
| 2 | 4 | 1 | 2 | 50.0% |
| 5 | 5 | 5 | 100.0% | |
| 6 | 10 | 12 | 83.3% | |
| 2 Total | 16 | 19 | 84.2% | |
| 3 | 7 | 999 | 2000 | 50.0% |
| 8 | 50 | 50 | 100.0% | |
| 9 | 12 | 22 | 54.5% | |
| 3 Total | 1061 | 2072 | 51.2% | |
| Grand Total | 1203 | 6601 | 18.2% |
However, when using the same calculation method in PowerBI Devide(Sheet1[Sold],Sheet1[Total],0), it doesn't show the same number especially after aggregation.
It show 49% in the grand total by averaging each Sold Rate item... That's not reasonable...
If I write Devide(sum(Sheet1[Sold]),sum(Sheet1[Total],0) , then every single row of Sold Rate shows 18.2%.
I just want it to be exactly the same as the data showed up in Excel pivot table. Any suggestions?
Hi joeguan,
In your scenario, please create a measure instead of a calculated column like below to calculate rate:
Rate = DIVIDE(SUM(Sheet3[Sold]), SUM(Sheet3[Total]),0)
Then create a matrix visual, place Category and Product in Rows, Sold, Total and Rate in Values.
Best Regards,
Qiuyun Yu
4 Replies
Hi joeguan,
In your scenario, please create a measure instead of a calculated column like below to calculate rate:
Rate = DIVIDE(SUM(Sheet3[Sold]), SUM(Sheet3[Total]),0)
Then create a matrix visual, place Category and Product in Rows, Sold, Total and Rate in Values.
Best Regards,
Qiuyun Yu- Hey Qiuyun, As I mentioned, if I use your formula, every single row shows 18.9%(the grand total number). I can't make a filter on category column though. Because it is always 18.9%...
- Ahhh, I got you. Create a measure instead of a column. Thanks so much!
Hi joeguan,
It seems that my solution works in your scenario. If that is a case, would you please mark a proper reply as an answer so that we can close the thread? :smileyhappy:
Best Regards,
Qiuyun Yu
