From the course: Excel PivotTables: Mastering PivotTables and PivotCharts

Show Values As: Difference From - Microsoft Excel Tutorial

From the course: Excel PivotTables: Mastering PivotTables and PivotCharts

Start my 1-month free trial

Show Values As: Difference From

“

- [Instructor] Alright next on our list, we've got two more show values to ask calculations difference from and percent difference from. And these are really, really helpful tools especially when you start getting into time series trending. So analyzing things like day over day, or month over month, or year over year trending, these difference from calculations can be unbelievably helpful. So let's go ahead and tweak our view a bit. Instead of showing country and genre, I'm going to pull country out and let's drag years in as secondary row labels. And you'll notice here that my gross revenue calculations, which are still set to percent of parent total, are giving me NA values because my base field was set to country, and country is no longer in this view. Again, a good reminder that these show values as calculations are tied to the specific view that you're looking at. So I can just cancel that and for now why don't we just revert back to no calculations so that we're looking at basic revenue totals. Now I've got a ton of data here, all sorts of years, I really only want to look at a small window, so I'm going to deselect them all, go down and let's just look at some more recent data from 2012 through 2015 and press okay. So now I'm looking at a four year sample broken down by genre with the sum of gross revenue totals. And what I'll do is just format these cells as a currency, and I'm going to leave this column in place, just as my baseline or my reference point, and pull in a second iteration, and this column, column D, will be the one that I apply my percent difference and my difference from calculations to. So just like with the percent of column and percent of row calculations, these are calculations that I could easily make myself as an analyst, it's just a manual step that I don't have to take and that I would prefer to automate. So when the question comes up, how has action movie revenue trended year over year, or what was the percent drop in biography revenue between 2014 and 2015, I obviously have all the information I need alone in column C to create those calculations and to answer those questions, but the show values ask calculation will allow me to do it on the fly with no manual step at all. So what I'm going to do here is right click show values as the difference from. And when you select difference from this dialog box opens up asking you to select a base field and a base item. Now the base field is the basis by which you're comparing changes, and since I'm looking for year over year trending, my base field will be year. Now for base item I have a few options here. The first option is to select one specific fixed year, like 2012 for instance, if I do that and press okay, and again we'll format these as currencies, now what this is showing me is revenue for each of these years, 2013, 14, and 15, each compared to 2012. So this number here tells me that action movie revenue in 2015 was about 140 million dollars less than it was in 2012. So that might be what I'm looking for, but what I actually find more valuable and more generally useful is rather than choosing a fixed year, scrolling up to the top and selecting this previous option. Now when I press okay this is giving me revenue changes year over year over year. So now this number is telling me that action movie revenue dropped 1.3 billion dollars between 2014 and 2015. So typically a better way to show trending using this type of calculation against the previous year. And then finally, I'm going to pull in a third instance of gross revenue, and this one I'm going to show the values as the percent difference from. Again, my base field is years, and my base item is previous, press okay, automatically formats to the percent, and as you can see what it's doing is translating these numbers in column D into percentages. So it's essentially just creating a different lens through which I can explain and communicate these year over year trends. So 2014 action movie revenue is up 51.7% year over year. And there you have it, difference from, percent difference from, really valuable tools when it comes to times series analysis.

Contents