cancel
Showing results 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

Regular Visitor

## Percentage across multiple categories

I need to calculate the ratio between visible years / weeks and a range of dates in order to calculate an uplift formula.

The problem statement is summed up in the below picture.

Please can you help me solve this problem, I've been banging my head against this for ages.

Power BI PBIX

🙂

2 REPLIES 2
Community Support

https://community.powerbi.com/t5/Desktop/Measuring-logged-workdays/m-p/613517#M292418

https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365

Best Regards,

Lin

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

Sorry but neither of these solutions seems to fit my problem.

Assume that we have multiple employees with multiple roles within the organisation. Each employee is expected to donate 100 hours of their time each year to each role. However, if an employee is only in a role for 1 month out of a year (say the role finished in January) then for the year that the role ended, they would only be expected to donate 8 hours (100/12).

Based on the data provided in the PBIX, I need to create a table that shows the following:

Year (e.g. 2019)

Week (e.g. 13)

Maximum Hours (e.g. 30 - based on number of employee roles * expected annual hours * visible years / visible weeks)

Multiplier (e.g. 0.5 - the equivalent of the 1 month out of 12 used in the example above. This needs to take into account the role start / end dates and their relationship to the visible weeks / years so that I can use it as a multiplier).

Expected Hours (e.g. 15 - Maximum Hours * Multiplier)

This will allow me to identify how much spare employee capacity I have for any given week and will help me with identifying who is available.

I hope that all made more sense, however, feel free to ping me if you need any further information.

As always, your help is massively appreciated.

🙂

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.