Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
Proud to be a Super User!
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
Proud to be a Super User!
How do you do what are you are explaining here and add in a column for year and month or just date?
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 🙂
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
101 | |
86 | |
64 |