Average Text In Pivot Table

[Bajan4eva1]

Good Afternoon,

This is my first post so apologies if I missed any of the rules or policies. I'd like to start of by saying that to those who post here since over the years the solutions have helped me alot when working in Excel. Hoping you all can help solve this next issue.

Summary: I have a table listing closed support tickets over the past few years which contain the columns in the 1st screenshot below. When I create a simple pivot I can get a Count of Owning Person (2nd screenshot) by month (rows) and year (columns) which is great, but I'd like to go a step further and add another column with the average per month for each owning person.

Issue: Because the Owning Person column is text, I'm unable to pull in an average.

I've tried the AverageX DAX formula, but I think I might be doing something incorrect. I can calculate the average manually by dividing the yearly totals for each person by 12 (e.g. row 5, 2nd screenshot), but do you think it's possible to add the average per month or each person using the pivot or a new measure with a DAX formula?

Mini Sheet:

SampleData_090422.xlsx
	A	B	C	D	E	F
1	Ticket ID	Ticket Type	Owning Team	Owning Person	Status	Closed Date Time
2	12345	Incident	Team A	Luthor, Lex	Closed	3/3/2019 14:22
3	12346	Request	Team A	Doe, John	Closed	2/22/2019 15:58
4	12347	Incident	Team A	Doe, John	Closed	8/24/2019 13:06
5	12348	Incident	Team A	Doe, John	Closed	2/24/2019 14:16
6	12349	Incident	Team A	Doe, Jane	Closed	3/7/2019 14:29
7	12350	Incident	Team A	Doe, John	Closed	3/1/2019 11:10
8	12351	Request	Team A	Doe, John	Closed	3/4/2019 13:38
9	12352	Request	Team A	Sparrrow, Jack	Closed	2/25/2019 16:05
10	12353	Incident	Team A	Doe, John	Closed	3/8/2019 14:57
11	12354	Request	Team A	Sparrrow, Jack	Closed	3/28/2019 17:09
12	12355	Request	Team A	Luthor, Lex	Closed	2/25/2019 11:20
13	12356	Incident	Team A	Sparrrow, Jack	Closed	6/10/2019 16:13
14	12357	Request	Team A	Doe, John	Closed	3/4/2019 12:54
15	12358	Incident	Team A	Doe, John	Closed	3/1/2019 15:25
16	12359	Request	Team A	Sparrrow, Jack	Closed	3/30/2019 7:59
17	12360	Request	Team A	Doe, John	Closed	3/15/2019 17:05
18	12361	Request	Team A	Luthor, Lex	Closed	4/13/2019 9:43
19	12362	Request	Team A	Doe, John	Closed	10/21/2019 12:10
20	12363	Request	Team A	Doe, John	Closed	3/4/2019 13:11
21	12364	Incident	Team A	Doe, John	Closed	2/28/2019 9:39
22	12365	Request	Team A	Doe, Jane	Closed	3/7/2019 12:53
23	12366	Request	Team A	Doe, John	Closed	3/1/2019 14:34
24	12367	Request	Team A	Doe, Jane	Closed	6/2/2019 16:32
25	12368	Request	Team A	Doe, John	Closed	3/28/2019 16:16
26	12369	Request	Team A	Doe, John	Closed	5/18/2019 11:28
27	12371	Incident	Team A	Jetson, Judy	Closed	12/30/2019 10:21
28	12372	Incident	Team A	Doe, Jane	Closed	3/7/2019 13:35
29	12373	Incident	Team A	Doe, Jane	Closed	3/30/2019 16:34
30	12374	Request	Team A	Doe, Jane	Closed	3/9/2019 14:08
31	12375	Incident	Team A	Doe, Jane	Closed	3/30/2019 16:41
32	12376	Incident	Team A	Doe, John	Closed	3/15/2019 12:22
33	12377	Request	Team A	Doe, Jane	Closed	3/9/2019 14:26
34	12378	Request	Team A	Doe, Jane	Closed	3/2/2019 16:14
35	12379	Incident	Team A	Doe, John	Closed	6/14/2019 11:23
36	12381	Request	Team A	Sparrrow, Jack	Closed	11/30/2019 11:28
37	12382	Incident	Team A	Lane, Lois	Closed	4/29/2019 10:09
38	12383	Request	Team A	Kent, Clark	Closed	4/4/2019 12:59
39	12384	Request	Team A	Doe, John	Closed	3/8/2019 16:40
40	12385	Incident	Team A	Doe, Jane	Closed	3/31/2019 12:44
41	12386	Incident	Team A	Doe, John	Closed	8/24/2019 13:09
42	12387	Incident	Team A	Doe, John	Closed	3/4/2019 13:06
43	12388	Request	Team A	Doe, Jane	Closed	3/9/2019 14:03
44	12389	Request	Team A	Doe, Jane	Closed	3/7/2019 14:10
45	12390	Incident	Team A	Doe, John	Closed	3/28/2019 14:50
46	12391	Incident	Team A	Sparrrow, Jack	Closed	4/6/2019 13:21
47	12392	Incident	Team A	Doe, John	Closed	6/15/2019 9:53
48	12393	Request	Team A	Doe, Jane	Closed	3/9/2019 14:54
49	12395	Request	Team A	Luthor, Lex	Closed	3/7/2019 9:47
50	12396	Request	Team A	Kent, Clark	Closed	3/29/2019 15:35
Data

Thanks and any guidance is appreciated.

 

[DRSteele]

Welcome to the Forum.

This is not exactly what you asked for, but I can help anyway.

I find it's better to add a column to the raw data Table instead of trying to tinker with the Pivot Table. Add a column that has a formula that reports the whatever "average per owning person per month' you're after. Then the Pivot Table will have another field which you can utilise.

 

[Bajan4eva1]

Welcome to the Forum.

This is not exactly what you asked for, but I can help anyway.

I find it's better to add a column to the raw data Table instead of trying to tinker with the Pivot Table. Add a column that has a formula that reports the whatever "average per owning person per month' you're after. Then the Pivot Table will have another field which you can utilise.

Thanks. I ended up just completing manually , but agree I will try that method. Appreciate the response.