Forum Discussion
Total Working Hours (repeating rows)
Hi all,
I have a similar table:
As you can see, there are many rows for one worker and one date. The problem is that worker A for example worked only 7 hours on 07/01/2018. I need to create a measure so when I put it into a table it would show the correct number (on all levels of aggregation, meaning month, week etc.) For now I use MAX, but as you can guess it works only if I do it /day.
Hope you understood what I mean and are able to help :)
These would be my desired outcomes
Hello, Anonymous
Try this formula
Working Hours =
SUMX (
ADDCOLUMNS (
SUMMARIZE ( Table[Date], Table[Date], Table[Worker] ),
"WorkHoursByWorkerAndDay", CALCULATE ( MAX ( Table[Hours] ) )
),
[WorkHoursByWorkerAndDay]
)
If you have Calendar (Date) table and relationship between Table and Calendar, use this formulaWorking Hours =
SUMX (
ADDCOLUMNS (
SUMMARIZE ( Table[Date], Calendar[Date], Table[Worker] ),
"WorkHoursByWorkerAndDay", CALCULATE ( MAX ( Table[Hours] ) )
),
[WorkHoursByWorkerAndDay]
)
1 Reply
Hello, Anonymous
Try this formula
Working Hours =
SUMX (
ADDCOLUMNS (
SUMMARIZE ( Table[Date], Table[Date], Table[Worker] ),
"WorkHoursByWorkerAndDay", CALCULATE ( MAX ( Table[Hours] ) )
),
[WorkHoursByWorkerAndDay]
)
If you have Calendar (Date) table and relationship between Table and Calendar, use this formulaWorking Hours =
SUMX (
ADDCOLUMNS (
SUMMARIZE ( Table[Date], Calendar[Date], Table[Worker] ),
"WorkHoursByWorkerAndDay", CALCULATE ( MAX ( Table[Hours] ) )
),
[WorkHoursByWorkerAndDay]
)