Forum Discussion
Solved
Calculate Total - Wrong Total Sum
Hello All-
I need to calculate the total hours worked by employees on a particular date AND specific store.
See data below and expected total hours column calculated.
| Date | Store | Employee # | Hours Worked | Total Hours |
| 01/04/2025 | IL207 | 208468355 | 7.72 | 42.25 |
| 01/04/2025 | IL207 | 211453196 | 7.9 | 42.25 |
| 01/04/2025 | IL207 | 214144669 | 7.57 | 42.25 |
| 01/04/2025 | IL207 | 216350876 | 4.93 | 42.25 |
| 01/04/2025 | IL207 | 216350884 | 7.68 | 42.25 |
| 01/04/2025 | IL207 | 331725085 | 6.45 | 42.25 |
| 01/04/2025 | IL213 | 208841502 | 7.6 | 13.32 |
| 01/04/2025 | IL213 | 327877239 | 5.72 | 13.32 |
| 02/04/2025 | IL214 | 206538415 | 6.22 | 67.93 |
| 02/04/2025 | IL214 | 209457290 | 8.2 | 67.93 |
| 02/04/2025 | IL214 | 212804512 | 8 | 67.93 |
| 02/04/2025 | IL214 | 213031131 | 6.3 | 67.93 |
| 02/04/2025 | IL214 | 213764079 | 6.2 | 67.93 |
| 02/04/2025 | IL214 | 314378134 | 8.27 | 67.93 |
| 02/04/2025 | IL214 | 319061750 | 6.07 | 67.93 |
| 02/04/2025 | IL214 | 327825824 | 6.25 | 67.93 |
| 02/04/2025 | IL214 | 328406574 | 6.4 | 67.93 |
| 02/04/2025 | IL214 | 345707210 | 6.02 | 67.93 |
I tried the below however an getting the wrong total sum
Total Hours =
CALCULATE(
SUM(Table[Hours Worked]),
ALLEXCEPT(Table, Table[Date], HILAN[Store])
)
Can someone help with a DAX formula.
Thanks!
Hi,
Please check the below picture and the attached pbix file.
It is for creating a calculated column.
CC Total Hours = VAR _date = Data[Date] VAR _store = Data[Store] VAR _t = FILTER ( Data, Data[Date] = _date && Data[Store] = _store ) RETURN SUMX ( _t, Data[Hours Worked] )
4 Replies
Hi,
Please check the below picture and the attached pbix file.
It is for creating a calculated column.
CC Total Hours = VAR _date = Data[Date] VAR _store = Data[Store] VAR _t = FILTER ( Data, Data[Date] = _date && Data[Store] = _store ) RETURN SUMX ( _t, Data[Hours Worked] )Got the same result as my DAX which is incorrect. The expected total hours vary
Date Store Employee # Hour Worked Total Hours 01/04/2025 IL207 208468355 7.72 81.99 01/04/2025 IL207 211453196 7.9 81.99 01/04/2025 IL207 214144669 7.57 81.99 01/04/2025 IL207 216350876 4.93 81.99 01/04/2025 IL207 216350884 7.68 81.99 01/04/2025 IL207 331725085 6.45 81.99 01/04/2025 IL213 208841502 7.6 13.32 01/04/2025 IL213 327877239 5.72 13.32 02/04/2025 IL214 206538415 6.22 76.01 02/04/2025 IL214 209457290 8.2 76.01 02/04/2025 IL214 212804512 8 76.01 02/04/2025 IL214 213031131 6.3 76.01 02/04/2025 IL214 213764079 6.2 76.01 02/04/2025 IL214 314378134 8.27 76.01 02/04/2025 IL214 319061750 6.07 76.01 02/04/2025 IL214 327825824 6.25 76.01 02/04/2025 IL214 328406574 6.4 76.01 02/04/2025 IL214 345707210 6.02 76.01
Please show the incorrect total calculation result
Date Store Employee # Hour Worked Total Hours 01/04/2025 IL207 208468355 7.72 81.99 01/04/2025 IL207 211453196 7.9 81.99 01/04/2025 IL207 214144669 7.57 81.99 01/04/2025 IL207 216350876 4.93 81.99 01/04/2025 IL207 216350884 7.68 81.99 01/04/2025 IL207 331725085 6.45 81.99 01/04/2025 IL213 208841502 7.6 13.32 01/04/2025 IL213 327877239 5.72 13.32 02/04/2025 IL214 206538415 6.22 76.01 02/04/2025 IL214 209457290 8.2 76.01 02/04/2025 IL214 212804512 8 76.01 02/04/2025 IL214 213031131 6.3 76.01 02/04/2025 IL214 213764079 6.2 76.01 02/04/2025 IL214 314378134 8.27 76.01 02/04/2025 IL214 319061750 6.07 76.01 02/04/2025 IL214 327825824 6.25 76.01 02/04/2025 IL214 328406574 6.4 76.01 02/04/2025 IL214 345707210 6.02 76.01
