Showing results for 
Search instead for 
Did you mean: 
ainsleybilton Frequent Visitor
Frequent 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.


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





Community Support Team
Community Support Team

Re: Percentage across multiple categories

hi, @ainsleybilton 

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


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


Best Regards,



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.
ainsleybilton Frequent Visitor
Frequent Visitor

Re: Percentage across multiple categories

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.



Helpful resources

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 364 members 3,526 guests
Please welcome our newest community members: