Percentage across multiple categories

[ainsleybilton]

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.

Thanks in advance for your help, you are awesome as always.

Power BI PBIX

🙂

[v-lili6-msft]

hi, @ainsleybilton 

Wasn't 100% understanding your problem, but if these post could help you:

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

If not your case, please share the expected output based on your sample data.

Best Regards,

Lin

[ainsleybilton]

Hi @v-lili6-msft 

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.

🙂