Distinct Count and Calculated field

[KLichter]

Hi there,

I would like to calculate the Average Order Value for my sales data mostly consisting of several rows/items per customer. The formula is: Value (€) / Distinct Count of Customers

Power Query is working fine for identifying the distinct count of customers (Col A), however, I found that in order to be able to make use of "Distinct Values" I need to load it to the Data Loader, which will deactivate the 'Calculated field' option for my Pivot table. However, I will need this to calculate the Average order value (€).

Could you please help me? It would be great to have the output in a pivot as a 'clickable' report for my users. Thank you!

 

[maabadi]

Use AVERAGEIFS Function

 

[maabadi]

Book1
	A	B	C	D	E	F	G	H	I
1	Customer	Value	Metric			Sum	No.	Average
2	A	182	Test2		Test1	774	3	110.571
3	A	53	Test2		Test2	677	4	96.7143
4	A	52	Test2		Total	1451	7	103.643
5	B	143	Test2
6	C	72	Test1
7	C	60	Test1
8	C	43	Test1
9	D	124	Test1
10	D	136	Test1
11	E	186	Test1
12	E	153	Test1
13	F	22	Test2
14	G	121	Test2
15	G	104	Test2
16
Sheet2

Cell Formulas
Range	Formula
F2:F3	F2	=SUMIFS($B$2:$B$15,$C$2:$C$15,E2)
G2:G3	G2	=SUM(--(FREQUENCY(IF($C$2:$C$15=E2,MATCH($A$2:$A$15,$A$2:$A$15,0)),ROW($A$2:$A$15)-ROW($A$2)+1)>0))
H2:H3	H2	=AVERAGEIFS($B$2:$B$15,$C$2:$C$15,E2)
F4	F4	=SUM($B$2:$B$15)
G4	G4	=SUM(G2:G3)
H4	H4	=AVERAGE($B$2:$B$15)
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[RoryA]

If you use Power Pivot, you can create whatever measures (the equivalent of calculated fields) you need, including a distinct count.

 

[KLichter]

Thanks guys! I used a measure from the data model in the end