Forum Discussion

spock1984's avatar
spock1984
Frequent Visitor
6 years ago
Solved

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 ...
Need Help
  • AnthonyTilley's avatar
    AnthonyTilley
    6 years ago

    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

    A1000.5
    B1200.4
    C1120.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 

AnthonyTilley's avatar
AnthonyTilley
Solution Sage
6 years ago

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

A1000.5
B1200.4
C1120.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 

spock1984's avatar
spock1984
Frequent Visitor
6 years ago

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 :)