Combine Filter and Aggregate Function?

[jakeman]

Is it possible to combine the Filter function with the Aggregate Function?

I have a table that spans several columns but I am interested in only two of them: Goal and % Completed. The Goal column has multiple rows that I want to Average the % Completed for. In the image attachment, my desired output would only show 6 rows with an average.

I've managed to get the first part shown in the attached image but I cannot figure the Aggregate function:

=FILTER(UNIQUE(FILTER(Plan_Tbl,(Plan_Tbl[#Headers]="Process / Function")+(Plan_Tbl[#Headers]="Goal")+(Plan_Tbl[#Headers]="Goal CP%"))),{1,0,1})

Would anyone be able to assist?

 

[Fluff]

How about

=LET(u,UNIQUE(Plan_Tbl[Process / Function]),HSTACK(u,AVERAGEIFS(Plan_Tbl[Goal CP%],Plan_Tbl[Process / Function],u)))

 

[jakeman]

Hey, Fluff - thank you for this solution. I'm nearly there.

However, the approach you gave me is just not quite there. The way you've arranged the formula causes the Average to take additional Goals into the calculation that I do not want. I want a distinct list of Process / Function, Goal, and Goal CP% first, and then an Average of the Process / Function once that happens.

Basically, the UNIQUE was supposed to take the below table:

Process / Function	Goal	Goal CP %
Product 1	Task 1	100%
Product 1	Task 1	100%
Product 1	Task 2	45%

and convert it to this:

Process / Function	Goal	Goal CP%
Product 1	Task 1	100%
Product 1	Task 2	45%

The formula you provided to me would yield an average of (100+100+45)/3 = 81.66 but what I'm looking for is actually (100+45)/2 = 72.5.

I really do appreciate your initial work on this. I have never used the LET function before and I'm trying to understand it and the HSTACK.

 

[Fluff]

Considering your image did not show the Goal column & your formula filtered it out, I didn't think it was important.
How about

=LET(u,UNIQUE(Plan_Tbl[[Process / Function]:[Goal]]),HSTACK(u,AVERAGEIFS(Plan_Tbl[Goal CP%],Plan_Tbl[Process / Function],INDEX(u,,1),Plan_Tbl[Goal],INDEX(u,,2))))

 

[jakeman]

Sorry, Fluff - I led you down the wrong path. Apologies for my lack of clarity. Let me start back from the beginning. Your solution is so close, I know it.

The the table on the right in the attached image is what I'm after. The problem is that I cannot get the right number of records to be Averaged in the formula you've provided to me previously, because there are some non-unique records being Averaged which are throwing my numbers off.

The below formula gets me the correct unique records that I need to Average but I cannot figure out how to Average this created array. I'm afraid if I go back to the original array and try to perform an Average on it, my results will continue to be skewed. That being said, is it possible to use the below formula as is and add in the AVERAGE IF and HSTACK functions around it?

=FILTER(UNIQUE(FILTER(Plan_Tbl,(Plan_Tbl[#Headers]="Process / Function")+(Plan_Tbl[#Headers]="Goal")+(Plan_Tbl[#Headers]="Goal CP%"))),{1,0,1})

 

[Fluff]

Can you please post some sample data using the XL2BB add-in & explain clearly what you are after.

 

[jakeman]

Sample Data.xlsx
	A	B	C	D	E	F	G	H	I
1	ID	Process / Function	Goal	Task	Task %CP	Goal %CP		Process / Function	Ave Goal %CP
2	1	Product 1	Data Migration	Task 1	33%	46%		Product 1	46%
3	2	Product 1	Data Migration	Task 2	25%	46%		Product 2	65%
4	3	Product 1	Data Migration	Task 3	50%	46%
5	4	Product 1	Data Migration	Task 4	75%	46%
6	5	Product 2	Training & Development	Task 1	100%	100%
7	6	Product 2	Training & Development	Task 2	100%	100%
8	7	Product 2	Training & Development	Task 3	100%	100%
9	8	Product 2	Training & Development	Task 4	100%	100%
10	9	Product 2	Curriculum Development	Task 1	30%	30%
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
Sheet1

 

[Fluff]

So you only want to average the Task1 for each product, is that correct?

 

[jakeman]

The above table represents the results I'm looking to achieve. I want to average the unique value for Goal.
So in the case of Product 2, there are two unique Goal values (Training & Development and Curriculum Development). If I average all of the Goal %CP I will get an inflated number (100*4 + 30)=86% but I really want to take the average of the unique Goal values for each Process/Function which would be (100+30)=65%

 

[Fluff]

Ok, how about

=LET(u,UNIQUE(Plan_Tbl[Process / Function]),d,CHOOSECOLS(UNIQUE(FILTER(Plan_Tbl,(Plan_Tbl[#Headers]="Process / Function")+(Plan_Tbl[#Headers]="Goal")+(Plan_Tbl[#Headers]="Goal %CP"))),1,3),HSTACK(u,BYROW(u,LAMBDA(br,AVERAGE(FILTER(INDEX(d,,2),INDEX(d,,1)=br))))))