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

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

maabadi

Well-known Member
Use AVERAGEIFS Function

maabadi

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

Similar threads

Replies
0
Views
136
Replies
7
Views
93
Replies
1
Views
306
Replies
0
Views
23
Replies
1
Views
687

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.

Threads
1,152,127
Messages
5,768,288
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.
Allow Ads at MrExcel

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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