21

How to Calculate Daily Averages with a Pivot Table

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

Watch on YouTube and give it a thumbs up.YouTube Thumbs Up LikeYouTube Subscribe Logo Excel Campus

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.

Source Data Multiple Rows per Date for Average Daily Sales Calculation

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.

Average Calculation versus Average of Total Daily Sales in Pivot Table

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.)

Daily Average Trend by Year Month and Region with Pivot Table and Pivot Chart

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.

Insert Pivot Table and Add this Data to the Data Model Checkbox Power Pivot DAX Formulas

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:

  1. Total Sales = SUM([Amount])
  2. Distinct Day Count = DISTINCTCOUNT([Date])
  3. Daily Average = [Total Sales]/[Distinct Day Count]

To create a measure:

  1. Right-click the Table name in the Pivot Table Fields List.
    Add Measure from Pivot Table Field List by Right-Clicking Table Name
  2. Select Add Measure.  The Measure Window will appear
  3. In the Measure Name box type: Total Sales
  4. In the Formula box type: =SUM([Amount])
    Create a DAX Measure in the Pivot Table for Total Sales
  5. 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.

New Measures Add to Bottom of Pivot Table Fields List Power Pivot

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.

Power Pivot DAX Measures Explicit Measures versus Implicit Measures

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.

Add Measure Fields to Values Area of 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.

Power BI Dashboards & Data Course Logo 557x316

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.

Add Date Field to Pivot Table and Group by Month Quarter Year

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.

Subtotals Turned Off for Daily Average Year Quarter Month Rollup

To turn the Subtotals on in a pivot table:

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

Subtotals Displayed at Top of Group Pivot Table

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.

Compare Daily Average Trends Time Periods Categories Pivot Chart

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.

Pivot Tables Webinar Banner 550

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

Click here to register for the Free webinar on Pivot Tables

Please share
Jon Acampora
 

Welcome to Excel Campus! I am excited you are here. My name is Jon and my goal is to help you learn Excel to save time with your job and advance in your career. I've been an avid Excel user and VBA developer for 10+ years. I am also a Microsoft MVP. I try to learn something new everyday, and want to share this knowledge with you to help you improve your skills. When I'm not looking at spreadsheets, I get outdoors and surf. :) more about me

Click Here to Leave a Comment Below 21 comments
Kayda - August 29, 2018

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

Reply
Qian - August 26, 2018

I cannot find distinct count in my pivot table calculation.

Reply
Shankhajit - August 13, 2018

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

Reply
    Free auto approve list 8-9-2018 - August 24, 2018

    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.

    Reply
Bill vonZangenberg - July 10, 2018

Great video and explanations.

Reply
Edil - June 28, 2018

Thx Jon. Awesome!

Reply
Alice Coston - June 28, 2018

Is the free webinar eligible for CPE?

Reply
    Jon Acampora - July 2, 2018

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

    Reply
veda - June 27, 2018

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.

Reply
    Jon Acampora - July 2, 2018

    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! 🙂

    Reply
Mo - June 27, 2018

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?

Reply
    Jon Acampora - July 2, 2018

    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.

    Reply
Haley - June 27, 2018

another great tutorial Excel Lord !

Reply
Puyu - June 27, 2018

Great tutorial
Thanx Jon

Reply
SteveT - June 27, 2018

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

Reply
    Jon Acampora - June 27, 2018

    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! 🙂

    Reply
Pernille - June 27, 2018

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

Reply

Leave a Reply: