topic Sum max value in DAX Commands and Tips

Sum max value

James_Galis1 — Tue, 18 Jul 2023 03:08:33 GMT

Hello

Hoping to get some assistance with a measure that adds up the value in the " Free time" column for each given date.

I use a date slicer which could show x number of days, weeks etc

The free time column represents available time for each respective date but should not be summing up all the values as it would give an inaccurate total of free time. Needing to add just 1 value from the " Free time" column for each date

The data is represented as follows:

Employee	Start Date	Start Time	End Time	Free Time
Emp 1	03/07/2023	07:00:00	8:00:00	2
Emp 1	03/07/2023	08:30:00	09:30:00	2
Emp 1	03/07/2023	10:00:00	11:30:00	2
Emp 1	03/07/2023	11:45:00	13:45:00	2
Emp 1	03/07/2023	14:30:00	16:30:00	2
Emp 1	05/07/2023	08:30:00	09:30:00	1.75
Emp 1	05/07/2023	10:00:00	11:00:00	1.75
Emp 1	05/07/2023	11:30:00	13:30:00	1.75
Emp 1	05/07/2023	14:00:00	15:30:00	1.75
Emp 1	05/07/2023	15:45:00	17:45:00	1.75
Emp 1	04/07/2023	08:30:00	09:30:00	1.5
Emp 1	04/07/2023	09:45:00	10:45:00	1.5
Emp 1	04/07/2023	11:15:00	12:45:00	1.5
Emp 1	04/07/2023	13:00:00	14:00:00	1.5
Emp 1	04/07/2023	14:30:00	16:30:00	1.5

The expected results for each date would be

3/7/23 = 2

4/7/23 = 1.75

5/7/23 = 1.5

Total = 4.75

Would like to show the "Total" value on a card if possible

I have tried a number of solutions but given that values for each date are the same any calculation seems to be adding up all the values for the respective date

Appreciate the assistance

Re: Sum max value

eliasayyy — Tue, 18 Jul 2023 08:35:07 GMT

you can use sumx function

sumx(values([date]),max([amount]))

Re: Sum max value

James_Galis1 — Tue, 18 Jul 2023 09:13:04 GMT

It does not seem to add up correctly

I get a total of 6 when looking at the data above

Re: Sum max value

eliasayyy — Tue, 18 Jul 2023 09:29:22 GMT

do you have multiple employees id?

Re: Sum max value

James_Galis1 — Tue, 18 Jul 2023 12:03:41 GMT

Yes. I do have multiple emp ID's associated with the data

Sample data

Employee	Start Date	Start Time	End Time	Free Time	Employee ID
Emp 1	03/07/2023	7:00:00 AM	08:00:00	2	1678290
Emp 1	03/07/2023	08:30:00	09:30:00	2	1678290
Emp 1	03/07/2023	10:00:00	11:30:00	2	1678290
Emp 1	03/07/2023	11:45:00	13:45:00	2	1678290
Emp 1	03/07/2023	14:30:00	16:30:00	2	1678290
Emp 1	04/07/2023	8:30:00 AM	09:30:00	1.5	1678290
Emp 1	04/07/2023	09:45:00	10:45:00	1.5	1678290
Emp 1	04/07/2023	11:15:00	12:45:00	1.5	1678290
Emp 1	04/07/2023	13:00:00	14:00:00	1.5	1678290
Emp 1	04/07/2023	14:30:00	16:30:00	1.5	1678290
Emp 1	05/07/2023	08:30:00	09:30:00	1.75	1678290
Emp 1	05/07/2023	10:00:00	11:00:00	1.75	1678290
Emp 1	05/07/2023	11:30:00 AM	13:30:00	1.75	1678290
Emp 1	05/07/2023	14:00:00	15:30:00	1.75	1678290
Emp 1	05/07/2023	15:45:00	17:45:00	1.75	1678290
Emp 2	04/07/2023	08:00:00	09:00:00	4	2546973
Emp 2	04/07/2023	9:00:00 AM	10:00:00	4	2546973
Emp 2	04/07/2023	14:00:00	15:00:00	4	2546973
Emp 2	05/07/2023	13:30:00	15:30:00	0	2546973
Emp 3	05/07/2023	10:00:00	12:00:00	2.5	3753414
Emp 3	05/07/2023	14:30:00	14:45:00	2.5	3753414

Re: Sum max value

eliasayyy — Tue, 18 Jul 2023 12:24:40 GMT

please try

Breaks = SUMX(VALUES('Table'[Start Date]) , CALCULATE(MAX('Table'[Free Time]),ALLEXCEPT('Table','Table'[Start Date],'Table'[Employee ])))

Re: Sum max value

nickc_innova — Tue, 18 Jul 2023 21:20:57 GMT

You might try:

SUMX( SUMMARIZE('Table','Table'[Start Date], 'Table'[Free Time]), [Free Time])