Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Power BI Weighted Average Help

Hi -  I am attempting to create a weighted average based upon the following:

 

Data resides in one table. The significant columns are 'days outstanding', 'invoice amount', and invoice#.

 

Ex - 100 days O/S x $10,000 (inv amount) = $1,000,000 for invoice# 123

         10 days O/S x  $1,000 (inv. amount) =  $    10,000 for invoice# 123

        Weighted average = 91.8 days based upon $1,010,000 / 11,000

 

Once I have accumulated weighted averages at the transaction level, I will then need to average them for various groupings (date, product, etc.).  

 

The built-in average feature does not provide what I need. Any assistance will be appreciated.

 

Thank you,

Gerry

  

Need Help

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi Anonymous,

    I am not sure what is the raw data in your table.

    I make a test using the table shown in first screenshot, I create a new column and measure using formulas below to calculate Weighted average.

    Column = Table5[days outstanding ]*Table5[invoice amount]
    Weighted Avg = SUM(Table5[Column])/SUM(Table5[invoice amount])




    If the above formulas doesn’t help, please help to share sample data of your table and post expected result here.


    Thanks,
    Lydia Zhang