Forum Discussion
Weighted average
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
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
3 Replies
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
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 :)
How do you do what are you are explaining here and add in a column for year and month or just date?
