Forum Discussion

Yak2792's avatar
Yak2792
Regular Visitor
6 years ago
Solved

Weighted Average every 24 hours

Hi All,   I'm trying to help automate a report that is currently manually entered in Excel.  I've got the first part of it, but I need some help with a weighted average between 2 shifts bonus.  The...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi Yak2792 ,

    First, please add one Index field for the related table in Power Query Editor. Then create a calculated column to get the average of Weighted Bonus:

    Weighted Bonus = 
DIVIDE (
    CALCULATE (
        MAX ( 'Bonus'[Bonus Calculation] ),
        FILTER ( ALL ( 'Bonus' ), 'Bonus'[Index] - 1 = EARLIER ( 'Bonus'[Index] ) )
    ) * 12 + 'Bonus'[Bonus Calculation] * 12,
    24
)

    add index column

    create calculated column

    Best Regards

    Rena

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi Yak2792 ,

Could you please provide the structures of these three table and sample data(exclude sensitive data) using screen shots just like the one in below screen shot? Please also provide the formula if there is any measure be created. It is better if you can provide your sample PBIX file. I need to check if there are other feasible ways to achieve your requirement based on the above information you provided.Thank you.

table structures and sample data

Best Regards

Rena

Yak2792's avatar
Yak2792
Regular Visitor
6 years ago

So I ended up finding a table similar to the one you listed above containing the shift by shift bonus numbers.  I followed your instructions above and everything works great now.  Thank you so much for your help, I will mark your post above as the solution.