topic Re: Calculate % of hours worked base two different working schedual using a calculated table/measure in DAX Commands and Tips

Calculate % of hours worked base two different working schedual using a calculated table/measure

Anonymous — Mon, 15 May 2023 22:36:04 GMT

Hello Power BI community, I will need some help for this task.

I have two working schedule, the first one include working on saturday for 8 hours whereas the second one only works for 5 hours on that day. I created a calculated column to have a full calendar (removing the sundays) and specify the number of hours per day :

The total number of hours worked and the names are on a non calculated table.

The objective is to calculate depending on the person the % of hours worked (filtred by the number of days)

In short : I want to devide "number of hours worked" by "sum work hour normal/exception" depending on the person.
Thank you for your time and help!

Re: Calculate % of hours worked base two different working schedual using a calculated table/measure

amitchandak — Tue, 16 May 2023 01:56:11 GMT

Anonymous , You already have that part of your table

divide(Sum(Table[Work Hour Normal]), Sum(Table[Work Hour exception]) )

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Re: Calculate % of hours worked base two different working schedual using a calculated table/measure

Anonymous — Tue, 16 May 2023 08:33:56 GMT

Thank you, but this does not help me sadly ; i created a new pbix document to share in hope of help (but it seems I can't share it here).
here are examples of all table used :

ID_R	R	R_date
1	work1	01/05/2023
2	work2	01/05/2023
3	work3	02/05/2023
4	work4	06/05/2023
5	work5	06/05/2023
6	work6	08/05/2023

ID_R_person	ID_R	person	time taken (h)
1	1	A	3,00
2	1	B	3,00
3	2	C	2,00
4	2	A	3,00
5	4	B	4,00
6	5	C	3,00
7	5	D	3,00
8	6	D	4,00
9	6	B	4,00

What i need to do :

person	hours spent on task	what i want
A	6,00	0,11
B	11,00	0,20
C	5,00	0,09
D	7,00	0,13207547

here are the calculate column used and they DAX function :

-Calendar = CALENDAR("01/12/2022","31/12/2023")

-IsWeekday = WEEKDAY(Calendrier[Date],2) <= 6

(!) this is the one used for the filter :
-Date without sunday =

var CalendarYearMonth = Calendrier[Date]

var LoadedAtYearMonth = Calendrier[IsWeekday]=true

return

if ( Calendrier[IsWeekday]= true,

CalendarYearMonth

)

-work hours exception = IF(WEEKDAY(Calendrier[Date],2)=6,5, IF(WEEKDAY(Calendrier[Date],2) =7,0,8))

-work hour normal = IF(WEEKDAY(Calendrier[Date],2)<=6,8,0)

these will give the total of hours worked depending on the date filter.

A,B and C needs to be divided by 56 whereas D needs to be divided by 53. In my case I only have two people that are exception to the rule, that i can identify with their name.

I am sorry I can't share the pbix document but I did my best to help you help me, in hope of having a solution to this issue, thank you again!