cancel
Showing results for
Did you mean:
Helper IV

## Calculated column by using group by other columns.

Hi All,

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

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

 Name Date Task Hours spent Expected Output column Raj 20-09-2016 111 2 6 Raj 20-09-2016 112 4 6 Raj 21-09-2016 113 1 1 Raj 22-09-2016 114 6 10 Raj 22-09-2016 115 4 10 App 20-09-2016 116 3 6 App 20-09-2016 117 2 6 App 20-09-2016 118 1 6 App 22-09-2016 119 4 15 App 22-09-2016 120 6 15 App 22-09-2016 121 5 15 Pom 20-09-2016 122 8 16 Pom 20-09-2016 123 4 16 Pom 20-09-2016 124 1 16 Pom 20-09-2016 125 3 16 Pom 21-09-2016 126 1.5 1.5 Pom 22-09-2016 127 3.5 3.5 Ken 23-09-2016 128 2 9 Ken 23-09-2016 129 1.5 9 Ken 23-09-2016 130 2.5 9 Ken 23-09-2016 131 3 9
7 REPLIES 7
Microsoft

@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

Community Champion

@aktripathi2506 In desktop, go to query editor -> under Transform tab -> click Group BY and as below to get desired result.

Helper IV

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.

Community Champion

@aktripathi2506

Try this

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

It should work

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

Cheers

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!
Helper IV

Thank you @CheenuSing and @v-ljerr-msft for detailed reply,

But with the suggested solution from both of you I have following problem:

I am having 2 different table table 1 and table 2 which are connected to each other. and Name and date are coming from two different table.

Hours spent and date is from table 1

name is from table 2

@CheenuSing With earlier I am not able to use the other table's column.

@v-ljerr-msft I don't know if there is a way to create a new calculated column which takes input from two different tables.

Thank.

Microsoft

@aktripathi2506

I don't know if there is a way to create a new calculated column which takes input from two different tables.

Yes, RELATED and RELATEDTABLE functions should work in this scenario. Could you screenshoot the relationship between your tables and post some sample data in your case?

Regards

Community Champion

@aktripathi2506 In that case you can leverage GROUPBY and SUMMARIZE functions of DAX.

Announcements

#### 2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.