Download How to Calculate Total Daily Averages with Pivot Tables
How to Calculate Total Daily Averages with Pivot Tables
How to 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.
Download the Excel file: /redirect?event=video_description&redir_token=...
Learn how to calculate the average of a daily total amount across month, quarters, years, or even categories with a pivot table.
Download the Excel file: /redirect?event=video_description&redir_token=nbO8C4FGJSYHChttAfikDZkZC6B8MTU0MDM2MDgyN0AxNTQwMjc0NDI3&q=https%3A%2F%2Fwww.excelcampus.com%2Fpivot-tables%2Fdaily-average-calculation%2F&v=MDZ2Ti5iEu0

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:

Read the full article here: /redirect?event=video_description&redir_token=nbO8C4FGJSYHChttAfikDZkZC6B8MTU0MDM2MDgyN0AxNTQwMjc0NDI3&q=https%3A%2F%2Fwww.excelcampus.com%2Fpivot-tables%2Fdaily-average-calculation%2F&v=MDZ2Ti5iEu0

Grouping Dates in Pivot Tables: /redirect?event=video_description&redir_token=nbO8C4FGJSYHChttAfikDZkZC6B8MTU0MDM2MDgyN0AxNTQwMjc0NDI3&q=https%3A%2F%2Fwww.excelcampus.com%2Fpivot-tables%2Fgrouping-dates-pivot-table-versus-grouping-dates-source-data%2F&v=MDZ2Ti5iEu0

Calendar Tables and Fiscal Calendars: /redirect?event=video_description&redir_token=nbO8C4FGJSYHChttAfikDZkZC6B8MTU0MDM2MDgyN0AxNTQwMjc0NDI3&q=https%3A%2F%2Fwww.excelcampus.com%2Ftables%2Fcalendar-table-explained%2F&v=MDZ2Ti5iEu0
Show more
Loading...
You are about to download a video in MP4 format.
If you'd like to adjust the format or resolution, just select it here
Download
Download has already started, please wait 10 Seconds
Youtube Vimeo Metacafe Flicker Photobcket Dailymotion Google video Youku 56.com Break Ustream Xhamster Wat Wrzuta Worldstarhiphop Web of Stories Washington Post Vk Vidme Video Mega Videa Viddler Vee HD Twitter Facebook USA Discovery
Loading...