Excel Pivot table count number of orders from a customer per year-CodePudding

Excel

I'm creating a pivot table and need a column to describe 'average number of orders' made by customers each year.

I have:

Order date
Customer ID
Year

I'm thinking calculated feild, and using COUNT(Order date) to determine number of orders. But I need to be able to use that to get the average number of orders for each customer.

Any help would be appreciated.

Date set example:

Customer ID Order date
Xh015046    12/04/2016
X7615777    03/06/2016
X10062024   20/04/2017
X7615777    25/06/2016
X7615777    05/01/2017
Xh015046    15/04/2017
X10062024   09/07/2018
X7615777    10/07/2018
Xh015046    11/07/2018
X10062024   12/07/2018

Pivot table I want:

Year    Average Number of orders per customer
2016    1.5
2017    1
2018    1.333333333

CodePudding user response：

Within the Data Model (Power Pivot), create a new measure:

AverageOrders :=
VAR MyTable =
    SUMMARIZE (
        Table1,
        [Customer ID],
        "Order Count", CALCULATE ( COUNTROWS ( Table1 ) )
    )
RETURN
    AVERAGEX ( MyTable, [Order Count] )

Amend the table (Table1) and column (Customer ID) names in the above as required.

Then create a Pivot Table within the worksheet with Year in the Rows section and the above measure AverageOrders in the Values section.