How To Quickly Calculate Total Daily Averages With Pivot Tables

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

In this example, the source data contains data for a sales transaction on each row. There are multiple sales per day, and therefore multiple rows with the same date.

This daily average calculation allows us to sum up multiple rows of data for the same date, and find the average trend across weeks, months, quarters, years, etc.

For this technique, we will use the Power Pivot Data Model and DAX Measures. Don't let that scare you, as I explain it all step-by-step.

IMPORTANT: You will need Excel 2013 or later for Windows. That includes an Office 365 subscription. I will post another video in the future on how to do this on Excel 2010, or Excel for Mac.

Here are the 3 measures we create to calculate the Average Daily Sales:

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

The DISTINCTCOUNT function in DAX calculates the distinct count of days for any given filter context in the pivot table. We take Total Sales for the same intersection and divide it by Distinct Count of days (date column) to get the Daily Average.

The great part about this technique is that it works for any time period grouping or even comparison between categories like region, department, etc.

Links mentioned in the video:

00:00 Introduction
00:19 Average of Total Daily Sales
02:57 Insert a Pivot Table & Add to Data Model
03:59 Create the DAX Measures
04:44 Add the Measures to the Pivot Table

[pallavimurugeshprabhu]

I was using daily data for my calculation and this is one time saver when u want to work with average months and years values. Thank you Jon

[gurungr]

Love You..😘...was searching for hours to calculate distinct day in pivot....you saved my day...

[mrandrewbroomfield]

You are a life saver. I’ve been looking for days how to do this. Using the normal average number was just giving me wrong numbers. Thanks

[KyraMoonspell]

Sir, you have just saved my life. I know it's been five years since you created this video, but know it still helps! xD Thank you!

[thiagogatti]

Nice video Jon. Thank´s for sharing.
However I see a problem, which might also interest you.

If you divide the grand total by 365 you get the true daily average of one year, no matter if some days had sales or not. However, multiplying your "daily" average by the distinct number of days you will get a number greater than the grand total. It happens because the distinct number of days only takes the days that actually had sales. If you try any database which has some days without sales, then you see what I mean.

Example, I pick any database which has sales in just some days of the month. I use the filter in the pivot table to select only January and February.
Let´s say the total sales for Jan+Feb equals 3000 pieces.
Let´s say there were 15 distinct days of sales in January(31 days total) and 15 distinct days of sales in February (28 days total). So, this method would return 3000/(15+15)=100
But 100 is not the daily average, it is the daily average of only the days that had sales.

I would like to know the average daily sales, that thing you get when you divide the total by the total amount of days, no matter if there were sales or not, like it should return 3000/(31+28) = 50.85

Please, how to get those 50.85 for any dynamically chosen selection of date filters Jon?
:)

[marymenant]

Wow you have the clearest videos. Your a great teacher. My problem is my daughter is 11 and she is a barrel racer. I am wanting to create a graft on her times over the year of 2018. She also competes in several different races.

[sleepless]

You are awesome!!! This video just solved my question that I have been searching results for hours!!! Thank you and enjoyed all your videos!!!

[thetreefellane]

an excellent tutorial - thanks for taking time to break this down

[aliciaanderson]

Very clear to understand. This helped me greatly. Thank you.

[immaa.]

I am very new to excel formula. Dax measures is quite new and interesting to use with pivot tables.
I have definitely learnt something new today.
Thank you so much for sharing.
My reference point from now onwards.

[chercat]

This trick helped me a lot! I was doing different pivot tables to see Qtr and Daily average now I just need to use one! Thank you so much John!!

[maramirez]

What a great video, I have been scratching my head for days trying to figure this out, but thankfully your video is available. Thanks again, lifesaver!

[Dude]

Excellent instruction. I wish I had found it earlier!

[andresgutierrez]

Muy buen video, me ahorró muchisimo tiempo! Gracias esde Colombia1

[tomanderson]

Hi Jon, thanks a lot. You helped me save time in my university dissertation.

[senthil]

Wow ! That was awesome! I didn't knew this before. I have learned something new today. Thanks

[yohcruz]

Simple yet very informative 👏🏻👍🏻 Thank you!

[carralegend]

Thanks for a great video! I'm new to excel and have been looking for exactly this measurement.


Keep up the good work! :)

[amineunshakable]

Hey John

My question to you (which I could t find anywhere in the all YouTube thread is this:

How can do the average days within the chart (example 1-3 days, 3-5 days) for turns around time spent on calculating the above

[nateblack]

I subscribed....Great job, very helpful explanation!