Forum Discussion

MLax's avatar
MLax
New Member
3 years ago
Solved

Time sum per row then column

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.

 

 

Need Help
  • bolfri's avatar
    bolfri
    3 years ago

    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. 🙂

     

8 Replies

  • galaamri's avatar
    galaamri
    Helper I
    3 years ago
    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]
  • MLax's avatar
    MLax
    New Member
    3 years ago

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

      • MLax's avatar
        MLax
        New Member
        3 years ago

        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. 

  • bolfri's avatar
    bolfri
    Solution Sage
    3 years ago

    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's avatar
      MLax
      New Member
      3 years ago

      Here is the data sample. 

      User NameNP Elapsed TimeNP Elapsed Time Other
      User1 00:24:00
      User1 00:45:00
      User100:11:00 
      User2 00:16:00
      User200:22:00 
      User200:24:00 
      User200:28:00 
      User2 00:30:00
      User2 00:36:00
      User2 01:00:00
      User2 01:05:00
      User2 01:30:00
      User204:40:00 
      User 3  00:30:00
      User 3  00:35:00
      User 3  00:40:00
      User 3  01:05:00
      • bolfri's avatar
        bolfri
        Solution Sage
        3 years ago

        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. 🙂