1 to Many table relationship. Sum the many's and calculate average

[MyThumbsClick]

Hi All
I have 2 tables linked: AllTickets and AllTimeEntries like below in a 1 ticket can have many time entries relationship.

I am trying to figure out how I can visualize an average of the total time entries per ticket. Because there can be multiple time entries, I need to sum the entries per ticket first and then do the average.

For the below example, the answer would be 1hr 8m

Ticket 1

time entry 1 = 5m

time entry 2= 30m

Ticket 2

time entry 1 = 2hr

Ticket 3

time entry 1 = 20m

time entry 2= 30m

etc

Whats the best way to go about achieving this?

[h21]

Hi @MyThumbsClick 

Is it possible for you to share the sample data and also an example of how you want output. 

It will be helpfull to work with sample data.

Thanks.

[MyThumbsClick]

Hi h21

I "fat fingered" it and accepted your reply as the solution. Can you undo that?

Example Data:

Tickets Table:

subject	id	priority	status	source	created_at	updated_at
Example ticket 1	19480	4	5	1	24/04/2024	26/04/2024 09:36
Example ticket 2	19479	2	5	1002	24/04/2024	24/04/2024 09:27
Example ticket 3	19478	2	5	1002	24/04/2024	24/04/2024 09:27
Example ticket 4	19477	2	5	1002	24/04/2024	24/04/2024 09:27
Example ticket 5	19476	2	5	1002	24/04/2024	24/04/2024 09:27
Example ticket 6	19475	2	5	1002	24/04/2024	24/04/2024 09:27
Example ticket 7	19474	2	5	1002	24/04/2024	24/04/2024 09:27
Example ticket 8	19473	2	5	1002	24/04/2024	24/04/2024 09:27
Example ticket 9	19472	2	5	1002	24/04/2024	24/04/2024 09:27
Example ticket 10	19470	1	3	2	24/04/2024	29/04/2024 12:57
Example ticket 11	19469	2	2	1001	24/04/2024	24/04/2024 08:00
Example ticket 12	19468	2	2	1001	24/04/2024	24/04/2024 10:20
Example ticket 13	19467	1	5	1002	23/04/2024	23/04/2024 22:02
Example ticket 14	19466	2	4	1	23/04/2024	29/04/2024 09:50
Example ticket 15	19465	2	5	1002	23/04/2024	24/04/2024 08:58
Example ticket 16	19464	2	5	1002	23/04/2024	26/04/2024 14:35
Example ticket 17	19463	3	5	1	23/04/2024	26/04/2024 09:36
Example ticket 18	19461	1	5	2	23/04/2024	25/04/2024 17:35
Example ticket 19	19460	2	5	1002	23/04/2024	23/04/2024 17:07
Example ticket 20	19459	2	5	1002	23/04/2024	26/04/2024 11:35
Example ticket 21	19456	1	5	2	23/04/2024	25/04/2024 16:35
Example ticket 22	19455	1	2	2	23/04/2024	29/04/2024 11:48
Example ticket 23	19454	1	5	2	23/04/2024	24/04/2024 11:20

Time Entries Table:

ticket_id	time_entry_id	time_spent	executed_at	workspace_id	agent_id
19480	52000881732	00:05	24/04/2024 09:27	2	52000034458
19470	52000882038	00:20	24/04/2024 10:10	2	52000014474
19466	52000887335	00:10	25/04/2024 16:30	2	52000014461
19466	52000888938	00:30	26/04/2024 11:00	2	52000014461
19466	52000882577	01:15	24/04/2024 10:15	2	52000014461
19464	52000883245	00:10	24/04/2024 13:51	2	52000034459
19463	52000881429	00:10	24/04/2024 08:44	2	52000014474
19461	52000880737	00:05	23/04/2024 17:30	2	52000034458
19459	52000883619	00:05	24/04/2024 15:02	2	52000034459
19459	52000881508	00:30	24/04/2024 17:30	2	52000034456
19459	52000882394	00:30	24/04/2024 11:02	2	52000034456
19459	52000881515	01:00	23/04/2024 17:00	2	52000034459
19459	52000882393	00:30	24/04/2024 10:30	2	52000034459
19456	52000880566	00:05	23/04/2024 16:31	2	52000034458
19455	52000881500	00:30	24/04/2024 17:00	2	52000034456
19455	52000883774	00:30	24/04/2024 15:00	2	52000034456
19455	52000892596	03:00	29/04/2024 09:00	2	52000034456
19454	52000880277	00:10	23/04/2024 15:32	2	52000034458

(I have removed some columns to keep charecter count down.)

Im trying to create a measure that displays the average total time spent on tickets on a card visualisation.

[h21]

Hi @MyThumbsClick 

Do you want something like below: 

[MyThumbsClick]

Thanks for this. What is [Total Time Spent]? Is that another measure? 

[h21 · Accepted solution]

Hi @MyThumbsClick 

Yes, that is another measure as below :

Total Time Spent = 
SUM(
   'Time Entries Table'[time_spent]
)

Average Total Time Spent = 
AVERAGEX(
    VALUES(
        'Tickets Table'[id]
    )
    ,[Total Time Spent]
)

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards.

[MyThumbsClick]

Thanks. I think i have 1 issues:

A slicer on the page is interefing with the visual/measure and i just get (Blank). The slicer is scoped to Tickets Table[created date]. If i exclude the interaction with the visual/measure, I get a result. I do want to keep the slicer though because i want to slice based on when the ticket was created.

[h21]

Hi @MyThumbsClick 
 Is that issue is happening everytime when you selecting the slicer or for some selection of the slicer only. 
Or other way is you can try plotting the data from the Date Table you have created or if you haven't created the Date Table. Than for once, try plotting the date from the Date Table. 

Best Regards.

[h21]

Hi @MyThumbsClick ,

I can't undo the action for the solution. That will be undo by you only.

Thank you for sample data.