Forum Discussion

aktripathi2506's avatar
aktripathi2506
Helper IV
9 years ago

Calculated column by using group by other columns.

Hi All,

 

I want to calculate total hour spent by the employee for a day

 

Please find below the example

I have also included the desired column which I am trying to get.

 

NameDateTaskHours spent Expected Output column
Raj20-09-20161112 6
Raj20-09-20161124 6
Raj21-09-20161131 1
Raj22-09-20161146 10
Raj22-09-20161154 10
App20-09-20161163 6
App20-09-20161172 6
App20-09-20161181 6
App22-09-20161194 15
App22-09-20161206 15
App22-09-20161215 15
Pom20-09-20161228 16
Pom20-09-20161234 16
Pom20-09-20161241 16
Pom20-09-20161253 16
Pom21-09-20161261.5 1.5
Pom22-09-20161273.5 3.5
Ken23-09-20161282 9
Ken23-09-20161291.5 9
Ken23-09-20161302.5 9
Ken23-09-20161313 9
Need Help

7 Replies

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Microsoft Employee
    9 years ago

    aktripathi2506

     

    In this scenario, you can use a Filter with VAR function to SUM Hours Spent for a day for a specific Name. See my sample below.

     

    I assume you have a table called MyTestTable like below.

    Then you should be able to use the formula below to create a calculate column to get Hours Spent for a day for a specific Name.

    Hour Spent For A Day = 
VAR name1 = MyTestTable[Name]
VAR date1 = MyTestTable[Date]
RETURN
    CALCULATE (
        SUM ( MyTestTable[Hours spent] ),
        ALL ( MyTestTable ),
        FILTER ( MyTestTable, MyTestTable[Name] = name1 && MyTestTable[Date] = date1 )
    )

     

    Regards

    • aktripathi2506's avatar
      aktripathi2506
      Helper IV
      9 years ago

      Thank you ankitpatira,

       

      But I am afraid that I can not try this way, as in actual senario I am using the data from 3 table which are related to each other (by building the relationship). 

       

      So name is coming from Table 1, group is coming from table 2 and hour is coming from table 3.

       

      So i was thinking to do it by using a DAX formula. Because with Dax formula I can use these columns from different table by using the related function.

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        aktripathi2506

         

        Try this 

         

        RunningTotal= SumX(
        Filter ( YourTable, YourTable[Name] = Earlier ( YourTable[Name]) && YourTable[Date] <= Earlier(YourTable[Date]) ),
        YourTable([HoursSpent])
        )

         

        It should work

         

        If this solves your issue please accpet this as a solution and also give KUDOS.

         

        Cheers

         

        CheenuSing