Weighted AVG Aggregation

[joeguan]

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?

[v-qiuyu-msft · Accepted solution]

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

[joeguan]

Ahhh, I got you. Create a measure instead of a column. Thanks so much!

[v-qiuyu-msft]

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?

Best Regards,
Qiuyun Yu

[joeguan]

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%...