How can I combine times from different tables?

[Anonymous]

Hello. I have 2 different tables showing time a ticket or task was worked, and I need to be able to show the total amount of time worked between a ticket and its associated tasks. A ticket may have more than one task associated with it. 

Example data:

TICKETDATA
Ticket Number	Time worked in Seconds
TIC001	254
TIC002	327
TIC003	500
TASKDATA
Ticket Number	Task Number	Time Worked in Seconds
TIC001	TASK00001	180
TIC001	TASK00002	125
TIC002	TASK00003	320
TIC003	TASK00004	84
TIC003	TASK00005	150
TIC003	TASK00006	95

So TIC001 has 2 tasks associate with it, besides the time worked in the ticket itself. So the total time between the 254 seconds from the ticket, and the time worked between the two tasks should be 559. Is there a way I can get PowerBI to total this between the two tables?

[v-kelly-msft · Accepted solution]

Hi  @Anonymous ,

If you dont wanna create a relationship,create a measure as below:

Total = 
var _sum=CALCULATE(SUM('TASKDATA'[Time Worked in Seconds]),FILTER(TASKDATA,'TASKDATA'[Ticket Number]=MAX('TICKETDATA'[Ticket Number])))
Return
IF(ISINSCOPE(TICKETDATA[Ticket Number]),_sum+SUM('TICKETDATA'[Time worked in Seconds]),SUMX(ALL(TICKETDATA),'TICKETDATA'[Time worked in Seconds])+SUMX(ALL(TASKDATA),'TASKDATA'[Time Worked in Seconds]))

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Ashish_Mathur]

Hi,

Create another 1 column table with a unique list of ticket numbers.  Create a relationship between the ticket number column of both tables to this thid table.  To your visual, drag Ticket number from this third table.  Write this measure

Time worked = sum(ticketdata[time worked in seconds])+sum(taskdata[time worked in seconds])

Hope this helps.

[Anonymous]

Hi @Anonymous 

There should be a one to many relationship between those two tables:

And then just a simple measure could help you get the total time for a ticket:

Total Time = SUM(TASKDATA[Time Worked in Seconds]) + SUM(TICKETDATA[Time worked in Seconds])

The result in a table would look like this:

Jesus.