Sum Avg Based on Distinct Field

[JPSailes]

Not sure how best to ask as a subject line.

col Box has additional line level details (using color to demonstrate scenario) thus repeating size. I need to only count size one time for each unique box. So my total for size would be 11; avg size 2.2. Effectively remove duplicates.

Typically, I would use two pivots tables to solve. However, I believe there should be a better route via pivot calculation or power pivot. I'm new to power pivot and cannot find a solution that works.

I was thinking if average of distinct box then sum average. Or to state another way: sum distinct box size average.

Box	Size	Color
A	2	Red
A	2	Green
A	2	Blue
B	1	Green
B	1	Blue
C	3	Red
C	3	Blue
D	2	Red
D	2	Green
D	2	Blue
E	3	Red

 

[lrobbo314]

Book1 (version 1).xlsb
	A	B	C	D	E	F	G
1	Box	Size	Color			365	Older
2	A	2	Red			2.2	2.2
3	A	2	Green
4	A	2	Blue
5	B	1	Green
6	B	1	Blue
7	C	3	Red
8	C	3	Blue
9	D	2	Red
10	D	2	Green
11	D	2	Blue
12	E	3	Red
Sheet5

Cell Formulas
Range	Formula
F2	F2	=AVERAGE(INDEX(UNIQUE(A2:B12),,2))
G2	G2	=AVERAGE(IF(FREQUENCY(IF(A2:A12<>"",MATCH(A2:A12,A2:A12,0)),ROW(A2:A12)-ROW(A2)+1),B2:B12))
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[JPSailes]

Book1 (version 1).xlsb
	A	B	C	D	E	F	G
1	Box	Size	Color			365	Older
2	A	2	Red			2.2	2.2
3	A	2	Green
4	A	2	Blue
5	B	1	Green
6	B	1	Blue
7	C	3	Red
8	C	3	Blue
9	D	2	Red
10	D	2	Green
11	D	2	Blue
12	E	3	Red
Sheet5

Cell Formulas
Range	Formula
F2	F2	=AVERAGE(INDEX(UNIQUE(A2:B12),,2))
G2	G2	=AVERAGE(IF(FREQUENCY(IF(A2:A12<>"",MATCH(A2:A12,A2:A12,0)),ROW(A2:A12)-ROW(A2)+1),B2:B12))
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

I'd like to do it within a pivot so I can include other information -- especially date.

 

[lrobbo314]

I loaded the table into the data model and created a measure that can be dropped into a pivot table.

Measure:

DA:=DIVIDE(CALCULATE(COUNTA([Box]),ALLEXCEPT(Table1,Table1[Box])),DISTINCTCOUNT([Box]),0)

And here's the result.

Book1 (version 1).xlsb
	B	C
3	Box	DA
4	A	3
5	B	2
6	C	2
7	D	3
8	E	1
9	Grand Total	2.2
Sheet4