Calculated column by using group by other columns.

[aktripathi2506]

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.

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

[v-ljerr-msft]

@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

[ankitpatira]

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

[aktripathi2506]

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.

[CheenuSing]

@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

[aktripathi2506]

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.

[v-ljerr-msft]

@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

[ankitpatira]

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