Calculate Total - Wrong Total Sum

[khisla]

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!

[Jihwan_Kim · Accepted solution]

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] )

[khisla]

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

[FBergamaschi]

Please show the incorrect total calculation result

[khisla]

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