**Bottom line:** Learn how to calculate the average of a daily total amount across month, quarters, years, or even categories with a pivot table.

**Skill level:** Intermediate

## Download the Excel File

You can download the Excel files below. The BEFORE file just contains the source data, and can be used to follow along with the video. The AFTER video contains the solution and sample pivot tables.

Pivot Table Daily Averages - Data Model - BEFORE.xlsx (36.0 KB)

Pivot Table Daily Averages - Data Model - AFTER.xlsx (235.7 KB)

## Average of Total Daily Sales

Kevin, a member of The Pivot Ready Course, asked a great question about calculating the average of total daily sales in a pivot table.

In this case his data set contained a sales transaction in each row. There are multiple sales per day, so the dates will repeat in the Date column.

If we use the regular Average calcluation type in the pivot table, the result will be the average amount per transaction. This is NOT what we want.

Instead, we want to see the average daily total across a larger time period like months quarters or years.

## Why Analyze Daily Averages?

The daily average metric can be useful in comparing trends for daily totals across time period (months, quarters, etc.) or even categories (regions, departments, days of the week, etc.)

We can use a line chart or column chart to quickly see how the daily average changes over time.

## How the Calculation Works

**IMPORTANT NOTE:** *You will need Excel 2013 or later for Windows for this method. This includes Office 365. Unfortunately, this will not work on Excel 2010, or the Mac versions of Excel.*

This technique uses DAX measures in the Power Pivot Data Model. We are going to use the **DISTINCTCOUNT** function to calculate the unique number of days in the data set. DISTINCTCOUNT is a DAX function in Power Pivot.

We then divide Total Sales by the Distinct Day Count to get the Average of Total Daily Sales. **This can be any amount (numeric value). It does NOT have to be sales data.**

[Average of Total Daily Sales] = [Total Sales]/[Distinct Day Count]

Here are the steps to creating the pivot table and measures.

### Step 1: Insert a Pivot Table & Add to Data Model

The first step is to insert a pivot table from your data set.

Important: Click the “Add this data to the Data Model” checkbox on the Create PivotTable Window.

This will add the data to Power Pivot and allow us to write DAX measures for the Distinct Count calculation.

### Step 2: Create the DAX Measures

Next we need to create the measures. We are going to create explicit measures for three calculations:

- Total Sales = SUM([Amount])
- Distinct Day Count = DISTINCTCOUNT([Date])
- Daily Average = [Total Sales]/[Distinct Day Count]

To create a measure:

- Right-click the Table name in the Pivot Table Fields List.

- Select
**Add Measure**. The Measure Window will appear - In the Measure Name box type: Total Sales
- In the Formula box type: =SUM([Amount])

- Click OK.

The measure will be created and added to the bottom of the Fields List. Repeat the steps above for the other two measures.

#### Implicit vs Explicit Measures

For the first two formulas we could also create the calculations by dragging the Amount and Date fields into the Values area, then changing the calculation type as need. These are know as **implicit measures** when we use Excel to create the measure by dragging and dropping fields.

The other option is to write the formulas in the Measure window, like we did above. These are known as **explicit measures** because we clearly explained or defined them in the formula editor.

One advantage of the explicit measures is that we can continue to use them in other formulas or pivot tables. Now that we have defined the Total Sales measure, it will be available in all new pivot tables from the data model.

We can also re-use the Total Sales measure, as we did in the Daily Average measure, and continue to build more complex formulas with it. This saves time and makes formula writing more efficient in the long run.

There are also advantages when using Power BI and bringing the data model back into Excel. My friend Avi shows an example in this video where Implicit measures are not imported to Excel from Power BI.

### Step 3: Add the Measures to the Pivot Table

The next step is to add the measure fields to the Values area of the pivot table.

You do NOT need to add all of the measure fields to the pivot table. **The calculations will still work even if you only add the Daily Average field to the Values area.**

However, the Total Sales and Distinct Day Count fields can be a nice addition to the pivot table. They might help show additional trends or explain why a daily average is high/low for a specific period.

The same measures will also work in Power BI, since Power BI uses Power Pivot to create relationships and measures. Checkout my course on Dashboards & Data with Power BI to learn more.

### Step 4: Create Date or Category Groupings

We now need to add fields to the Rows and/or Columns Areas to create the trend report.

For this example we will add the Date field to the Rows area and Group it my Year, Quarter, and Month.

Checkout my article on Grouping Dates in a Pivot Table VERSUS Grouping Dates in the Source Data to learn more about date grouping.

I also have an article on The Calendar Table (Date Dimension) Explained for Power Pivot & Power BI. Calendar tables allow us to create relationships between different data sources. They can also be used if your company is on a fiscal calendar.

#### Make Sure Subtotals are Turned On

If you don't see numbers for the Year, Quarter, or Month lines, then the Subtotals are turned off in your pivot table. Here is a screenshot that shows how to turn the subtotals on.

**To turn the Subtotals on in a pivot table:**

- Select a cell inside the pivot table.
- Select the Design tab in the ribbon.
- On the Subtotals drop-down menu, select “Show all Subtotals at Bottom of Group” or “Show all Subtotals at Bottom of Group”.
- You should now see the subtotal calculations for the row area groupings (year, quarter, month).

### Step 5: Analyze the Trends

Finally we can add a quick pivot chart to analyze the trends of the daily averages. Here is a line chart that compares the Average Daily Sales by Month and Region.

We can see that the North region did better than the South in the last two months of the year. And the South had a declining trend after March.

## What Do You Use Daily Averages For?

So, that is one way to calculate daily averages with pivot tables using Power Pivot and DAX Measures. DAX measures are amazingly powerful, and this simple example only scratches the surface of their capabilities.

I'd love to know what you use daily average metrics for. Please leave a comment below and let us know. Thanks so much! ðŸ™‚

## Free Training on Pivot Tables

If you are currently using pivot tables, but feel like you aren't getting the most out of them, then I have a **free training** just for you.

Right now I'm running my free training webinar on **“The 5 Secrets to Understanding Pivot Tables”**. During this 60 minute training I explain the critical steps to building pivot tables to prevent errors and truly understand how they work.

If you've been struggling to build pivot tables with your own data, getting error messages, or just not sure which fields to put in each area, then get registered for the webinar. We also talk about getting the source data in the right layout, which is the most critical step to building a pivot table.

The webinar is running at multiple days and times, so click the link below to register for a day & time that works for you.

Hi,

Nice tutorial but he results are not as expected in my case. I would like to have the daily average for every day of a certain month but I do not have sales for every day of the month(some day 2 some days none). Therefor I have missing dates and the calculation: “amount / days of the month” (which returns the actual daily average if I’m right) does return something completely else then the pivot table from this tutorial returns.

Am I missing something?

Thanks Es.

Hi there,

I have an excel spreadsheet which I think would benefit from this, but I’m not exactly sure how to use it. Here’s some example data:

Agent Policy Number V1 Mileage V2 Mileage V3 Mileage

A RB1111 10300 9000 2000

A RB2222 13000 5000 13000

A RB3333 12000 11000 10000

B RA1111 12500

B RA2222 7200 8000 8900

B RA3333 1500 3000

C RC1111 2000 9000 5000

C RC2222 9100 7000 12000

In this example, I want to Average Agent A’s mileage for all 3 columns (so it’s not just an average per policy number, but an average for all policies and mileage together).

I can easily pivot the averages for A, B and C, but it isn’t combined for ALL 3. Can you assist?

Thanks!

Kayda

Together with everything that seems to be developing within this subject matter, many of your opinions are actually relatively radical. However, I am sorry, but I can not subscribe to your whole strategy, all be it refreshing none the less. It seems to me that your remarks are not entirely rationalized and in actuality you are yourself not really entirely certain of your assertion. In any event I did enjoy examining it.

I cannot find distinct count in my pivot table calculation.

Hi Jon – hope you are doing fine ! I am not sure how to get about a particular scenario, so if you could please help me out I would be obliged. Is it possible to create a pivot table based on data in multiple worksheets and grouped by a composite field structure – eg. I have 4 sheets A, B, C, D each having Year and Month column plus some additional data columns (one is say ‘K’) (year and month have repeating rows)…now I want to consolidate (pivot / aggregate) all this data on a separate sheet grouped by Year, Month (rowwise) and K should be a filter and columns should be the name of sheets – A,B,C,D. waiting for your response.

Rgrds

Shankhajit

Iâ€™ve been having issues with my Windows hosting. It has set me back quite a bit while making the next list. This is the current list that I have. I should add another list in less than a week. Iâ€™ll let you all know when the next list is ready. Thank you for your patience.

Great video and explanations.

Thx Jon. Awesome!

Thanks Edil! ðŸ™‚

Is the free webinar eligible for CPE?

Hi Alice,

The webinar is not eligible for CPE at this time. Thanks!

Hi Jon,

I am using Excel 2013. I don’t see add measures when I right click on my table in pivot table. I had to go to power pivot and use calculated fields to achieve the same. Also I am not able to drill down on date. It tells drill down not possible here. Group option is also disabled.

Hi Veda,

Yes, in Excel 2013 you can add the Measures from the Power Pivot tab in the ribbon. This is different from a calculated field. You will also want to make sure the source data is added to the data model, then create the pivot table from the Power Pivot window or from the Insert tab.

Here’s an article that explains Why the Pivot Table Group Field Button is Disabled for Dates.

I hope that helps. Thanks again and have a nice day! ðŸ™‚

Thanks Jon. Really enjoyed the post.

When I added the grouped day/month/year fields to my Rows, they did not appear with the values for Total Sales, Day Count, Daily Average as they do in your vid. Is this a setting somewhere?

Hi Mo,

You might have to manually drag each field into the Rows area after grouping them. I believe this behavior depends on which version of Excel you are on.

I hope that helps.

another great tutorial Excel Lord !

Thanks Haley! ðŸ™‚

Great tutorial

Thanx Jon

Thanks Puyu! ðŸ™‚

Jon,

I was looking at just such a problem today. Lots of numbers for individual groups, but the user wanted the average for each user, to see who was slacking in finishing a particular report. A pivot table didn’t quite do the job, and I knew I’d need a formula, so was going to try with Power Query tonight; but I think I’ll give your approach first dibs!

Thank you!

SteveT

Hi SteveT,

That’s awesome! It sounds like you might be able to adapt this technique to find the average for each user. A lot depends on the structure of the data. It might make for an interesting example. If you want to send an sample file I’d be happy to take a look and see if it might make a good topic for another post. You can reply to the newsletter email and attach the file, if you want.

Thanks again! ðŸ™‚

Thanks so much for the post.Really thank you! Great.

Thanks Pernille! ðŸ™‚