Weighted average

[spock1984]

Hi everyone,

Please help me, I am loosing my mind trying to figure this out :((

My source data is like this:

Entity	Quantity	Factor
A	100
B	120
C	112
A		0.5
B		0.4
C		0.2

I need to the measure Factor  to be aggregated using weighted average (weight being the Quantity).

I tried SUMX as suggested on the forum, but in my case data is structured so that Quantity and Factor are in different rows (I cannot change it).

So SUMX would do 100*0 + 120*0 + 112*0 + 0*0.5 + 0*0.4 + 0*0.2 = 0

I need the formula to be (100*0.5 + 120 * 0.4 + 112 *0.2 ) / sum (100+120+112) = 0.36

Any hint how to acheive this? I will much appreciate

Entity	Sum of Quantity	Weighted average of Factor
A	100	0.5
B	120	0.4
C	112	0.2
Total	332	0.36

Please help 🙂

Thanks in advance, Grzegorz

[AnthonyTilley · Accepted solution]

first you need to summerize your table to bring the QTY and FACTOR inline 

go to Modeling at the top of the page and then select new table.

in the formula bar enter the code below 

Calculated_Table = SUMMARIZE('Table', 'Table'[Entity],"QTY" , Sum('Table'[Quantity]),"FACTOR",sum('Table'[Factor])  )

adjust for your actual table names and colunm names 

this will output your table with the values inline

EntityQTYFACTOR

A	100	0.5
B	120	0.4
C	112	0.2

add a calculated colunm for the wighted

Column = Calculated_Table[QTY]*Calculated_Table[FACTOR]

then create a measure to dum these and devide by sum of qty

Measure = DIVIDE(sum(Calculated_Table[Column]),sum(Calculated_Table[QTY]))

final out put 

[reh169]

@AnthonyTilley 

How do you do what are you are explaining here and add in a column for year and month or just date?

[spock1984]

Great!! Thanks for the hint. It put me back on track.

I actually was working in Excel Power Query at the moment, I will trasfer my model to PowerBI soon.

I modified the solution a little bit - as I could not find how to use SUMMARIZE in Excel.

1) I used in query editor the "Group By" function:

2) I was able to create a measure Avg_Factor = Sumx(Table1;[Sum_Qty]*[Sum_Fct])/Sum([Sum_Qty])

Works as a charm 🙂