Forum Discussion
Post PatronTotal working hours / week
HOURLY BRACKETS = VAR DRIVER = DriverStartStop[Driver] VAR WEEK = DriverStartStop[OUR NEW CREATED SUB-PERIOD] RETURN IF(CALCULATE(SUM(DriverStartStop[Decimal Hours Driver]), ALL(DriverStartStop), DriverStartStop[Driver]=DRIVER, DriverStartStop[weeknum]=WEEK)<20,"Less than 20hrs",
IF(CALCULATE(SUM(DriverStartStop[Decimal Hours Driver]), ALL(DriverStartStop), DriverStartStop[Driver]=DRIVER, DriverStartStop[weeknum]=WEEK)<=35,"Btw 20hrs & 35 hrs",
IF(CALCULATE(SUM(DriverStartStop[Decimal Hours Driver]), ALL(DriverStartStop), DriverStartStop[Driver]=DRIVER, DriverStartStop[weeknum]=WEEK)>35,"More than 35hrs")))Instead of referencing the week we now want to reference the new sub period that we are using instead (P1_S1 ... P1_S2 ... and so on)
Community ChampionI understand! :-)
I'm not quite sure if this works but check if you can toggle subtotals on column level,
Post PatronHi tex628,
I am really sorry but I keep on facing little issues with this one.
i have group the row to obtain total and I am wondering if I could only display the total and have something say P1 with the month above.
i have this option avaible and I thought the results was not matching the results of our new formua P1_S1 etc... Turns out that when I added the total with that new formula, the system also shows a total of 6 while the distinct count for the table shows 7 as well as the ID details.
- corange
Hi,
Thank you so much for your help. It is all working fine now.
- corange
Hi tex628,
Just forgot about what I just said. It must be the return from holiday that is playing a trick on me.
It is fine. I have got the right results in both instance.
The only thing I can't figure out is that 5+hours being classified as 35hours +. It is one of those week that was falling into two periods.
- tex628
Can you post the measure that you are currently using to calculate the hours?
- corange
Hi,
It is really weird what is happening. When I click on the count of ID for more than 35hours, ID 7173 shows up under this bracket but only display 5:40 hours. However, from another table, if I click on the same driver, the table updates and shows the correct amount.
My formula is
DifferenceDur = SUM(StartStop[LastActionDec]) - SUM(StartStop[FirstActionDec])
Decimal Hours (Hrs) = ([DifferenceDur])/60
- tex628
And for the grouping? :-)
- corange
Hi,
HOURLY BRACKETS = VAR DRIVER = DriverStartStop[Driver]
VAR WEEK = DriverStartStop[weeknum]
RETURN
IF(CALCULATE(SUM(DriverStartStop[Decimal Hours Driver]), ALL(DriverStartStop), DriverStartStop[Driver]=DRIVER, DriverStartStop[weeknum]=WEEK)<20,"Less than 20hrs",IF(CALCULATE(SUM(DriverStartStop[Decimal Hours Driver]), ALL(DriverStartStop), DriverStartStop[Driver]=DRIVER, DriverStartStop[weeknum]=WEEK)<=35,"Btw 20hrs & 35 hrs",IF(CALCULATE(SUM(DriverStartStop[Decimal Hours Driver]), ALL(DriverStartStop), DriverStartStop[Driver]=DRIVER, DriverStartStop[weeknum]=WEEK)>35,"More than 35hrs"))) - tex628
HOURLY BRACKETS = VAR DRIVER = DriverStartStop[Driver] VAR WEEK = DriverStartStop[OUR NEW CREATED SUB-PERIOD] RETURN IF(CALCULATE(SUM(DriverStartStop[Decimal Hours Driver]), ALL(DriverStartStop), DriverStartStop[Driver]=DRIVER, DriverStartStop[weeknum]=WEEK)<20,"Less than 20hrs",
IF(CALCULATE(SUM(DriverStartStop[Decimal Hours Driver]), ALL(DriverStartStop), DriverStartStop[Driver]=DRIVER, DriverStartStop[weeknum]=WEEK)<=35,"Btw 20hrs & 35 hrs",
IF(CALCULATE(SUM(DriverStartStop[Decimal Hours Driver]), ALL(DriverStartStop), DriverStartStop[Driver]=DRIVER, DriverStartStop[weeknum]=WEEK)>35,"More than 35hrs")))Instead of referencing the week we now want to reference the new sub period that we are using instead (P1_S1 ... P1_S2 ... and so on)
- tex628
No worries! :-)