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

     

bolfri's avatar
bolfri
Icon for Solution Sage rankSolution 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
    Icon for Solution Sage rankSolution 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. 🙂

     

    • MLax's avatar
      MLax
      New Member
      3 years ago

      Thank you so much this worked!