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

Yak2792's avatar
Yak2792
Regular Visitor
6 years ago

I should also mention that this is currently all done with measures, and is being presented in a table.

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    Can you share some sample actual raw data.

    • Yak2792's avatar
      Yak2792
      Regular Visitor
      6 years ago

      What raw data would you need?  The data is coming from 4 different SQL tables, so it might be a bit of a mess.  I have one table that has the calendar, 2 that contain product information, and another that contains bonus multpliers depending on certain criteria.

       

      Would the table presented in the initial post with details of how the bonus is calculated suffice?

      • Anonymous's avatar
        Anonymous
        Not applicable
        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