Time sum per row then column

[MLax]

Please help! I have tried so many different formulas and cant get it to work. Time calculation in DAX is complicated. 

I have a file that contains multiple elapsed times for the same user. Here is what I need:

#1 Obtain the sum of NP Elapsed Time per user.  

#2 Obtain the sum of NP Elapsed Time Other per user.

#3 Obtain the final sum of NP Elapsed Time sum+NP Elapsed Time Other sum. 

All final numbers for the calculations need to be in hh:nn:ss.

[bolfri]

Can you post here a sample data so we can copy and pase it into power bi or pbix file with data included? 🙂 It might be only this data that you showed on screen.

[MLax]

Here is the data sample. 

User Name	NP Elapsed Time	NP Elapsed Time Other
User1		00:24:00
User1		00:45:00
User1	00:11:00
User2		00:16:00
User2	00:22:00
User2	00:24:00
User2	00:28:00
User2		00:30:00
User2		00:36:00
User2		01:00:00
User2		01:05:00
User2		01:30:00
User2	04:40:00
User 3		00:30:00
User 3		00:35:00
User 3		00:40:00
User 3		01:05:00

[bolfri · Accepted solution]

Solution #1: Power Query M (I can show you how)

If you want, I can prepare a solution in Power Query M, so you will recive same information without loading every row in the Power BI (recommended), but if you need every row for some reason then go for solution 2.

Solution #2: DAX Calculated Table

Sample Agg = 
SUMMARIZE(
    'Sample',
    'Sample'[User Name],
    "Elapsed Time", SUM('Sample'[NP Elapsed Time]),
    "Elapsed Time Other", SUM('Sample'[NP Elapsed Time Other]),
    "Total time", SUM('Sample'[NP Elapsed Time]) + SUM('Sample'[NP Elapsed Time Other])
)

Don't forget to change this columns from datetime to time only. 🙂

Solution #3: Measures

If you don't want to create a new table. Only calculate values from the oryginal one - use measures.

Elapsed Time = SUM('Sample'[NP Elapsed Time])
Elapsed Time Other = SUM('Sample'[NP Elapsed Time Other])
Elaped Time Total = [Elapsed Time] + [Elapsed Time Other]

Don't forget to format this measures to time format. 🙂

[MLax]

Thank you so much this worked!

[MLax]

Unfortunately, that does not work. Too many arguments for SUMX.

[galaamri]

Then the Solution would be to convert the time to numbers then sum them up

Follow this video, It would help you.

https://youtu.be/Z1hGU93UE1c

[MLax]

I was able to calculate everything in decimal point, but im still unable to convert to hh:nn:ss. I need all calculations in time format. Changing the format of the column doesnt work. 

[galaamri]

Hi @MLax 

Try this measure. 

NP Elaps Time =

             SUMX(Table name,

                  HOUR(Table Bame [NP Elaps Time]), MINUTE(Table Bame [NP Elaps Time]) , SECOND(Table Bame [NP Elaps Time]))

NP Elaps Time Other =

             SUMX(Table name,

                  HOUR(Table Bame [NP Elaps Time Other]), MINUTE(Table Bame [NP Elaps Time Other]) , SECOND(Table Bame [NP Elaps Time Other]))

Total = [NP Elaps Time] + [NP Elaps Time Other]