Pivot Table showing just averages

[SandsB]

Column A = Technician ID
Column B = Patient Record ID
Column C = Record Age (Number of days since Patient Record was created)
Column D = Days Remaining (Number of days before Patient Record needs to be closed

There are a couple dozen Technicians and each could have anywhere from 10 to 50+ Patient Records in the input file. I need a report that shows, in one row, Technician ID, Total Number of Patient Records assigned to the Technician, Average Record Age, Average Days Remaining.

I can do a pivot table that gives me all this data but what I need is a report that shows one row per Technician with just the other 3 fields. I just don't see a way to make all the data for each agent appear on only 1 row.

 

[Sergius]

Hello! If I understand you correctly, this can be done using Power Query

Book1.xlsm
	A	B	C	D
10	Technician ID	Patient Record ID	Record Age	Days Remaining
11	Technician_1	Patient_1	2	3
12	Technician_1	Patient_2	5	4
13	Technician_2	Patient_1	4	2
14	Technician_2	Patient_2	3	3
15	Technician_2	Patient_3	8	4
16	Technician_2	Patient_4	5	2
17	Technician_2	Patient_5	9	5
18	Technician_2	Patient_6	5	3
19	Technician_3	Patient_1	4	1
20	Technician_3	Patient_2	6	2
21	Technician_3	Patient_3	5	4
22	Technician_3	Patient_4	2	5
23	Technician_3	Patient_5	1	2
24	Technician_3	Patient_6	1	5
25	Technician_3	Patient_7	8	3
26	Technician_3	Patient_8	4	4
27	Technician_3	Patient_9	4	3
28	Technician_3	Patient_10	2	3
Sheet10

let
    Source = Excel.CurrentWorkbook(){[Name="Table10"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Technician ID", type text}, {"Patient Record ID", type text}, {"Record Age", Int64.Type}, {"Days Remaining", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Technician ID"}, {{"Total Number of Patient Records", each Table.RowCount(_), Int64.Type}, {"Average Record Age", each List.Average([Record Age]), type nullable number}, {"Average Days Remaining", each List.Average([Days Remaining]), type nullable number}})
in
    #"Grouped Rows"

Book1.xlsm
	A	B	C	D
1	Technician ID	Total Number of Patient Records	Average Record Age	Average Days Remaining
2	Technician_1	2	3,5	3,5
3	Technician_2	6	5,666666667	3,166666667
4	Technician_3	10	3,7	3,2
Table10

 

[Asbestos_Jen]

This can be done in a pivot table, as well. I placed tech ID in the row label, and each of the other three columns in the Values section, changing count to average for the record age and days remaining.

 

[SandsB]

Didn't know I could do that. Thank you so much!

 

[SandsB]

Sergius - I went the other way but you started me down the road of Power Queries. I can see where I'll use this a lot. Thank you.