From the course: Excel PivotTables: Mastering PivotTables and PivotCharts
Unlock the full course today
Join today to access over 22,400 courses taught by industry experts or purchase this course individually.
Calculating using counts, part 1 - Microsoft Excel Tutorial
From the course: Excel PivotTables: Mastering PivotTables and PivotCharts
Calculating using counts, part 1
- [Instructor] Alright time for another pro tip and this one's about using calculated fields with counts. And if you recall in the intro lecture about calculated fields, I mentioned that any metric that's included in a calculated field calculation will be evaluated as a sum by default. There's actually a way around that, and I'm going to show you how it works. So first things first let's adjust our pivot here. Instead of data by country let's pull in data by rating. And only filter down to G, PG, PG-13, and R. And there you go, we can pull out profit, we can pull out ROI, those are the calculated fields that we had created. Keep in mind that they are still here available for us if we choose to use them later they don't disappear. And we're going to look at gross revenue. So looking at this view here we still have the five year time frame selected and we're looking at total revenues broken down by ratings. So looking at this view you may be tempted to say that PG-13 movies perform…
Practice while you learn with exercise files
Download the files the instructor uses to teach the course. Follow along and learn by watching, listening and practicing.
Contents
-
-
-
-
-
-
-
Value summarization modes4m 45s
-
Pro tip: Avoiding the "count of" trap3m 49s
-
Value calculation modes3m 35s
-
Show Values As: % of Column/Row3m 47s
-
Show Values As: % of Parent3m 2s
-
Show Values As: Difference From4m 42s
-
Show Values As: Running Total2m 44s
-
Show Values As: Rank2m 36s
-
Show Values As: Index8m 6s
-
Inserting calculated fields6m 30s
-
Calculations in PivotTables vs. source data4m 32s
-
Calculating using counts, part 17m 20s
-
Calculating using counts, part 24m 19s
-
Inserting calculated items6m 11s
-
Solve order and list formula tools4m 54s
-
-
-