cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
aktripathi2506
Helper IV
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

 

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
7 REPLIES 7
v-ljerr-msft
Microsoft
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.

table.PNG

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 )
    )

result.PNG

 

Regards

ankitpatira
Community Champion
Community Champion

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

 

Capture.PNG

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.

@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

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

Proud to be a Datanaut!

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.

@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

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

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors