# Distinct Count and Calculated field

#### KLichter

##### New Member
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!

### Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

##### Well-known Member
Use AVERAGEIFS Function

##### Well-known Member
Book1
ABCDEFGHI
1CustomerValueMetricSumNo.Average
2A182Test2Test17743110.571
3A53Test2Test2677496.7143
4A52Test2Total14517103.643
5B143Test2
6C72Test1
7C60Test1
8C43Test1
9D124Test1
10D136Test1
11E186Test1
12E153Test1
13F22Test2
14G121Test2
15G104Test2
16
Sheet2
Cell Formulas
RangeFormula
F2:F3F2=SUMIFS(\$B\$2:\$B\$15,\$C\$2:\$C\$15,E2)
G2:G3G2=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:H3H2=AVERAGEIFS(\$B\$2:\$B\$15,\$C\$2:\$C\$15,E2)
F4F4=SUM(\$B\$2:\$B\$15)
G4G4=SUM(G2:G3)
H4H4=AVERAGE(\$B\$2:\$B\$15)
Press CTRL+SHIFT+ENTER to enter array formulas.

#### RoryA

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

#### KLichter

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

Replies
0
Views
136
Replies
7
Views
93
Replies
1
Views
306
Replies
1
Views
687
Replies
6
Views
139

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,152,125
Messages
5,768,250
Members
425,460
Latest member
Astros1243

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back