Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Yak2792
Regular Visitor

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 white cells below are already calculated and correct.  The green is what I need help calculating.

 
 
 

Capture.JPG

The formula for this is ((Bonus Calculation*12)+(Next Shift BC*12))/24.  As an example, for Wednesday night shift, it would be the following:

 

((175.61%*12)+(198.93%*12))/24 = 187.27%

 

Thanks for any help that can be provided!

1 ACCEPTED SOLUTION

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 columnadd index column

create calculated columncreate calculated column

Best Regards

Rena

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

7 REPLIES 7
Yak2792
Regular Visitor

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

Can you share some sample actual raw data.

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?

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 columnadd index column

create calculated columncreate calculated column

Best Regards

Rena

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thank you for the reponse @v-yiruan-msft .

 

Your solution would be super easy, but unfortunately these are not stored in a table, they are calculated measures, as well as columns from data pulled from 3 different tables.  You can see this below.

 

Capture.JPG

 

Is it possible to create a new table with this information in it?  I looked at this link, but in my case it won't work becasue the tables I'm trying to join are different sizes, one is a shift calendar and the other is the product information.

 

I guess as a fall back I could always enter data manually and then use the solution you provided, but ideally I would like to automate this as much as possible, as this report will be used by somebody else who is not a familiar with Power BI.

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 datatable structures and sample data

Best Regards

Rena

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors