In this video, the instructor explains why you should always create calculated rate metrics (cost per click, conversion rate, and so on) as calculated fields within your PivotTable, as opposed to calculated columns within your raw source data.
- [Instructor] Alright, so in the last lecture I shared…with you guys a pro tip that basically said…never calculate rates or rate metrics…in your raw data itself…and to instead make those calculations…using PivotTable calculated fields.…Now it's a bit of a nuanced point but it's an important one…and I think it's worth digging into a little bit more.…So let me give you guys a demonstration…of why this is so important…and what the implications are of taking each approach.…So in this case we already calculated a field called ROI…which is calculated as the revenue divided by the budget.…
Now that's a good example of a rate metric.…So had I instead gone into my raw data…and inserted a brand new column here…and insert it right after Q and call it ROI raw,…I could just calculate that right in here…as column Q divided by S, which is budget,…and apply that down.…And I don't want to mess up the formatting…so I can go into the auto fill options,…fill without formatting, and I can just turn…that into percentage, really just to draw attention…
- Establishing a layer convention and property filters
- Structuring your source data
- How PivotTables actually work
- Number formatting and conditional formatting
- Sorting and grouping options
- Troubleshooting incorrect sorting
- Value summarization and value calculation modes
- Inserting calculated items
- Visualizing data with PivotCharts
- Designing interactive dashboards
Skill Level Intermediate
Learning Excel Data-Analysis (2015)with Curt Frye2h 24m Beginner
Part 1: Introduction1m 8s
1. Getting Started
Getting to know IMDb2m 21s
2. PivotTable 101
3. PivotTable Formatting
4. Sorting, Filtering, and Grouping
5. Calculated Values, Fields, and Items
6. Visualizing Data with PivotCharts
7. Wrapping Up
- Mark as unwatched
- Mark all as unwatched
Are you sure you want to mark all the videos in this course as unwatched?
This will not affect your course history, your reports, or your certificates of completion for this course.Cancel
Take notes with your new membership!
Type in the entry box, then click Enter to save your note.
1:30Press on any video thumbnail to jump immediately to the timecode shown.
Notes are saved with you account but can also be exported as plain text, MS Word, PDF, Google Doc, or Evernote.