How To Quickly Calculate Total Daily Averages With Pivot Tables

[@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!

[@ExcelCampus]

Glad it helped! 😀

[@gurungr2811]

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

[@pallavimurugeshprabhu404]

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

[@thetreefellane]

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

[@sleepless3598]

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

[@souravdas703]

Very Helpful. Easy to understand.

[@aliciaanderson9343]

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

[@Dude276175]

Excellent instruction. I wish I had found it earlier!

[@andresgutierrez7504]

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

[@sihlendlovu8167]

great video very helpful

[@ExcelCampus]

Glad it was helpful! 😀

[@KevanJeffrey]

Very clear and concise

[@amindhajayasena9022]

This was really helpful. Thank you.

[@immaa.7675]

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.

[@marymenant9661]

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.

[@tomanderson1588]

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

[@ozansen_ozibaba]

Thanks , sir . Excellent Tutorial 👍

[@maramirez254]

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!

[@alexzandertate6766]

a trick: watch series at Flixzone. I've been using them for watching a lot of movies recently.

[@kalebroberto2363]

@Alexzander Tate Definitely, I have been using Flixzone for since november myself :)

[@randymaverick372]

@Alexzander Tate Definitely, been watching on Flixzone for months myself =)

[@hemangjani5315]

This is amazing. Thanks for sharing !

[@chercat37]

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

[@tanderson596]

Thank you Jon from Excel Campus!

[@rosemaryfindley5206]

Great presentation. Thank you

[@pingucheng8981]

Voodoo useful....you are the star

[@Anikvikram]

It's marvelous

[@ballglamis]

Love it! Perfectly explained!

[@senthil4707]

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

[@yohcruz5815]

Simple yet very informative 👏🏻👍🏻 Thank you!

[@AnshuljainSanmati]

you are a life saver 😁

[@adamjonsson]

Very helpful!

[@jaimeerh]

Excel-lent technic, it helped me a lot.

[@thiagogatti6325]

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? :)

[@salvadorcanzoneri5584]

Good question. I have the same problem. If you find someone who explains this, please share. Thanks Thiago.

[@subinelias674]

Thank you. ✌🏻🙌🏻

[@carralegend23]

Thanks for a great video! I'm new to excel and have been looking for exactly this measurement. Keep up the good work! :)

[@datguyAndee]

Wow... This is like magic. Thank you so much!

[@rozidevidas]

that's great!! thanks!

[@nateblack972]

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

[@Sparqcorporatetraining]

Outstanding!

[@michaelfullard4983]

Great job Jon, thank you

[@ExcelCampus]

Thank you Michael! :-)

[@samiaalmamary8595]

I need help sir , in excel ,How do I create an Excel VBA button that will call a function?

[@DougHExcel]

Yeah for DAX measures!

[@ExcelCampus]

Woohoo! Thanks Doug! :-)

[@amineunshakable1295]

Hey John My question to you (which I could t find anywhere in the all KZbin 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

[@CordaSonoraAtelier]

Muito boa esta aula. Parabéns!

[@chamisoufiane1054]

how about plotting the chart you've shown at the start of the video?

[@shimaametwally586]

Many thanks, I have another question how we calculate max Daily sales if there is repeated date without considering repeated date as 2 dates

[@andrews7993]

Jon doesn't mention what to do when there is no add measure option.

[@nasrintaghavi6877]

Thanks for the excellent video, it helps a lot! I just have a question: What if I want daily averages, not total daily averages? I mean I have the depth to groundwater data for different wells over an 11-year period. I now want to create average daily, monthly, and average annual groundwater depth tables. Using your method, when I have more than one measurement in one day for a particular well, it sums it up instead of averaging it. I can send you my data if you like. I would really appreciate it if you could have a look. Thanks, Nasrin

[@angiepalmer5227]

Another great video! Have you ever worked with 2 date fields? A start and end date to show capacity by day?

[@jettynaveenkumarreddy3836]

hello, please explain how to calculate average with out add measure. Thank you

[@johnmaynor3851]

I need the data in the matrix to be the averages and only adding a separate column.

[@taramiller4029]

I was trying to figure this out for days. This helpped imensely. My Date period comes as Date and time so it sees each date/time as unique value (thus days are not unique) is there a way around having to uncouple and then recouple the date/time info?

[@valerieolivier1942]

Thanks a lot for this video, so clear ! I have a problem that I can’t resolve. I want to do an average by person on 12 month for each year Even if the person is not present each month. For exemple a person is present 4 month My table 28-1-2018 FRENCH Jessica 28-2-2018 FRENCH Jessica 28-3-2018 FRENCH Jessica 28-4-2018 FRENCH Jessica The average should be 4/12 ie 0,33 for 2018 Which Dax fonctions I should used ? Sorry for my approximative english😏

[@manojupmanyu2425]

Jon can u please share the video for doing the same in excel 2010.

[@jaimegonzalez7283]

Great tutorial. Once this is working, how can I add more data to the data source? Let's say I start with 6 months of data and I want to add month 7. I try it adding month 7 data to the end of my data source but excel see it like another table and does not update my pivot table... what to do?

[@mikechiks983]

Great presentation Jon! Thanks for sharing. Quick question: Do you need any Excel add in to be able to see the 'Add Measure' tab?

[@tevinpark1697]

Same question. It was never answered for a 1 year.. sigh

[@miltonmavhusa6828]

@@tevinpark1697 Same Question here. i'm not seeing the add measure option

[@maropus5152]

In creating a moving average trend line to a chart, are there several different measures that have to be created or does the calculation have to be done from the pivot table?

[@manojkumarmohanta5640]

I need to change the Auto SUM option in pivot table to Auto AVERAGE option. I have many columns & I need their average instead of Sum. Pls help me.

[@amaizingpeople1264]

Please share mtd and ytd sale forcasting fomuls

[@mattmatt245]

What if you want to take into account days when there was no sales ?

[@ExcelWithChris]

Hi Jon. I have a table recording manufacturing of tanks (time taken from start to end of a tank). I end up with a pivot showing a specific tank filtered, for a date and a operator etc. I can create in the pivot the different lines for the tank and times. I need at the end a column added to pivot showing in each line the average for the total pivot times. So it will be the same value for each row. I need it in there to put it in the column chart as an average line for the specific tank . I have tried all sorts of measures, but just cannot get it to work.

[@ExcelCampus]

Hey Chris, Great question! There are a few ways to solve this problem. One relatively simple way with a regular pivot table is to add a column in the source data with an AVERAGE or AVERAGEIFS formula to calculate the average number for each row. This should return the same number for each cell in the column, depending on the criteria. You can then add that new column to the values area and use the MAX or AVERAGE calculation type to return the same number for each cell in the values area. This will allow you to create a flat line on the pivot chart. This technique works well for simple reports and charts that aren't being filtered. I hope that helps. We'll add this to our list for future videos. Thanks again and have a nice day! 🙂

[@AnshuljainSanmati]

Can. U help how to calculate rolling average using pivot tables?

[@tapomoychakraborty2880]

Can it be done on office 2010?

[@zainabqd5970]

I want to update my daily data but I don’t want to sum the values please let me know how to do it

[@abhisuvin5852]

good day , thanks for video. my problem is that i have product A is 50 and product B is 50 and product C is 50 so my average shows in pivot table 50 is correct my Grand Total shows 50 again but i want to show 150. is there any way we can do that way.

[@mendez1223]

Q1= 52,97. Q2= 52,88. Q3=47,5. Q4=49,71. The grand total= 50.78??? Wrong. It's 50.765, I still have this problem with my pivot table too. There is a way to see the ACTUAL average of the year?

[@hagerismail578]

Hello, Thank you for this amazing video , but i don't have excel 365 can make formula with fields items &sets and i need to get average Hours / average by person , weekly , monthly , quarterly ,waiting your feedback ,,

[@dishydez]

Great video. I wanted to know is there a way to ger different period data without it being in the source data. So for instance, we can only use the pivot table to segment monthly because there is monthly data in the source data. But what if all I have is annual data and I want to calculate what this would be monthly and daily? Would this be possible/

[@sandyshum6287]

How to change to monthly average instead of daily average

[@muhammadfarisomar8581]

Hello John..Sorry..When i try to follow your video step by step..My excel failed to group the data set according to monthly quarterly and yearly..and i cannot generate a formula when i right click the button..it is due to the excel version..my excel is 2013 version?hope you can help me

[@fhandal14]

Great video Jon. Super useful!! I have an extra question, what if I wanted to make a percentage comparison between the days to see the trend?

[@BlakeC341]

Doesn't work in newer versions of Excel evidently. The table doesn't even show in the same manner in the Pivot Table Fields menu. Bummer.

[@pedroreis6172]

I am usung the latest version of excel on mac, and I can't find the same options...

[@ModernPindawale]

Sir I have one query about this question. Have you answer about adjustments calculations doing for an employes excel document Weekly Time sheet please. I'd also like the input areas to be asked as question in a pop-up format for the emplyee to answe/fill in?

[@HeinrichDortonBrand]

how do i do this with multiple measures and i also cant group the fields

[@ToDeoS]

Somehow every single field appears to be non-number format and unable to be processed as a number.. The data itself is indeed in number format.... Help anyone.? The only way to show any value is to show "Count of XXX", and SUM/AVG. etc. all gives an error saying "We can't summarize this field with Average because it's not a supported calculation for Text data types.".... Could this have anything to do with the fact that the data are formulas.? But the actual value in the data table is indeed numbers.

[@balajis6103]

Hi Jon, I am trying to right click on tblsales tab to select measures. But shows 'Remove from Active Tab'. I am using 2013. I am working on the data I have and not using the excel shared here. Could you please help? Thanks in advance!! - Balaji

[@rakiband3R]

Hi sir, I have a problem that, I use office 2010 in my office. I have an ODBC where I got our necessary sale data time to time by data refreshing. In the ODBC excel file I have 02 sheet. 1. Raw data and 2. Pivot sheet. In the pivot sheet I need to mark many cells data in different colors and save it for future data analyzing. But when I refresh the sheet all color marked got venish ! This is very painfull for me to find out the color marked data again. Kindly help me to solve this problem if you understand my problem. Thanks and Best Regards, Md. Rakib Hossain.

[@joebarrett5902]

I put a DAX table in a file with other pivot tables. Everything was looking great until I tried to save the file. I keep getting errors and the file will not save. I have tried everything I know. Please help!

[@wasimshaikh5794]

Hey Jon, I tried to download your file with provided link and also the new links which you hve provided in comment section is not working.please provide working link. Thanks 😀

[@ExcelCampus]

Hi Wasim, sorry about the trouble. The link is working. try again with www.excelcampus.com/pivot-tables/average-daily-total-calculation/ click the links of the files under "Download the Excel File". Hope that helps! 😀

[@wasimshaikh5794]

@@ExcelCampus Thanks Jon!! I got files from your website 😊

[@asanadidy22]

I have a problem to calculate the average can you help me if I send you the excel file

[@davidcortez1796]

The link to download the excel file doesn't work.

[@ExcelCampus]

Hello David, we are sorry about that. Please try this link www.excelcampus.com/pivot-tables/average-daily-total-calculation/

[@puneshaweerasinghe5912]

sir practicing exercise can't open

[@ExcelCampus]

Hello Punesha, we are sorry about that. Please try this link www.excelcampus.com/pivot-tables/average-daily-total-calculation/

[@hazemali382]

link doesn't work

[@ExcelCampus]

Hello Hazem, we are sorry about that. Please try this link www.excelcampus.com/pivot-tables/average-daily-total-calculation/

[@ModernPindawale]

How to create this work in Excel

[@ModernPindawale]

Help me

[@hagerismail578]

Hello, Thank you for this amazing video , but i don't have excel 365 can make formula with fields items &sets and i need to get average Hours / average by person , weekly , monthly , quarterly ,waiting your feedback ,,