How to calculate average of measure

[lazzarjovvch74]

Hello,

I am trying to calculate average value by Quarter directly in Power BI model but when I compare the result to what I got in Excel performing the same calculatioon, I see different result. I simplified a table that I used and it looks like this:

Quarter	Price	SF	$/Sf
1	54545	1212	45.00
1	32323	2121	15.24
2	23232	1212	19.17
2	34344	2121	16.19
3	23232	1212	19.17
4	23233	3232	7.19
4	45454	2323	19.57

My goal is to show average $/SF by Quarter in a clustered chart in Power BI. It is easy since I have ready $/SF calculated per each line in Excel.

But I tried to do this calculation directly in Power BI and I got different result. 

I used the table from above as data soirce in Power BI and this is how I calculated Avg S/SF:

$/SF Avg = AVERAGEX(Sheet1,DIVIDE(SUM(Sheet1[Price]),SUM(Sheet1[SF])))

This is a table that shows Avg $/SF value by Quarter (from Excel) compared to Avg $/SF by Quarter calculated in Power BI. Does anyone know why there is a difference?

	Avg  Excel	Avg Power BI
1	30.1	26.1
2	17.7	17.3
3	19.2	19.2
4	13.4	12.4

Thank you.

[Sean · Accepted solution]

@lazzarjovvch74

Get rid of the 2 SUM functions inside the DIVIDE just reference the columns

[jthomson]

Looks like Power BI is adding up the prices and SF values before dividing them to get your average, whereas Excel is doing each line individually and then taking the average of each individual line's result

[lazzarjovvch74]

It seems like that. How could I do in Power BI what Excel does with Average function? That is my goal, to do that directly in Power BI.

[jthomson]

---

@lazzarjovvch74 wrote:

It seems like that. How could I do in Power BI what Excel does with Average function? That is my goal, to do that directly in Power BI.

---

It's probably possible by making a calculated column that divides one by the other, sticking that column into a visual and using average as an option, if that's intended behaviour - by your method, if I've got two people selling pens and one person sells 99 red pens and no blue pens, and another sells no red pens but one blue pen, and I've got a calculation that has proportion of total pens sold that are red, Power Bi would give 99% whereas your method would give 50%...

[lazzarjovvch74]

Thank you all guys for the explanantion. I'm still growing in DAX.  It works now.

Best