Year To Date and Variance with a Pivot Table using Summarize By

now let's talk about the different summarize options that are available within pivot tables such as a running total or which would be like a year date total or something like a variance so let's do this by first I'm going to dry I already have my revenue in my pivot table I'm going to drag my revenue in again notice how I have two revenue columns here now I'm going to now rename this to be year today revenue and so what I'm going to do here is basically get the year-to-date revenue by quarter so we can see the actual revenue in the quarter and then we can see the year-to-date as we proceed along the year now I have this spread new column in here and so now I'm going to right-click it and I'm going to I'm going to choose um summarize values by I'm going to choose more options and here I'm going to click on show value as and then we're going to choose running total and then that's going to note it's going to ask what my base still the base item is I'm going to choose day click OK and now you can see here this format this to make this look good build settings number format okay you can see the first quarter my year date value on is exactly the same as q1 but then in q2 it's added the previous quarter same with q3 and q4 so it makes a nice way to to get my year-to-date values by quarter so let's say maybe now we want to get the variance so we want to know how our revenue is changing by quarter is our revenue going up or is it going down let's add our the revenue column into the data area again and then I'm going to call this variance and then now again let me go ahead and come format this now I'm going to right-click it show summarize more options show value as and let's find variants or actually it's it I'm calling a variants bits its difference from so we're different from a prior column which is essentially the variants and then the base field going on Tuesday and then the base item is previous because we want the various from the previous column so notice how there's no variance for q1 for q2 our revenue went up by $1400 q3 it changed and went down by $361 and q4 it went down as well by $1,500 so there's just different different in various ways to show the data as it changes from column the column to essentially basically sunrise your your data in different ways not just with a sum or counter average but actually on the fly show on different calculations this would take a lot of hand calculations if you had to do this on your own or put in formulas but the pivot table does this very nicely with with very little effort

Channel: Reporting Guru

Views: 22,998

Rating: 4.8823528 out of 5

Keywords: Summarize By

Id: q5M20UwxY1Q

Length: 4min 48sec (288 seconds)

Published: Wed Aug 22 2012