BUA 345 - Lecture 4

Descriptive Analytics with Pivot Tables

Housekeeping

HW 2 is due Monday, 1/27/25

Demo videos for HW 2 are posted

Today’s plan

• Descriptive Analytics
  • Pivot tables
  • Summarizing data in Tables
  • Summarizing data ‘On the Fly’ with Slicers
• HW 2 Demo
• R and R/Studio Resources for BUA 345

In-class Polling (Session ID: bua345s25)

Lecture 4 In-class Exercise - Q1

Open the data file Lecture 4 Review Question Data in the Blackboard section for Lecture 4.

• Save the Lecture 4 Review Question Data.xlsx dataset to your BUA 345 folder.

• Convert these data to an Excel table

• Remove Duplicates using all columns except OBS NO..

• Sort data by Price from ‘Largest to Smallest’

• Use the first indicator icon set to classify PRICE in Conditional Formatting.

• What is the lowest price in the ‘High’ (green checkmark) category?

  • Round answer to closest thousand to select correct choice.

Descriptive Statistics

• Describing and summarizing data quickly is an essential skill.

• Excel has a powerful set of tools for this purpose, Pivot Tables.

• To Access these tools:

  1. Convert our 2024 Movie Data to an Excel Table (Insert > Table)

  2. Click any cell within table, so that ‘Table Design’ tab is visible.

  3. Click Summarize with PivotTable.

Introduction to Pivot Tables

• When you click Summarize with PivotTable, you will see this pop-up which should be populated as follows:

1. Specify `Existing Worksheet.

2. Specify G1 for the location.

If PivotTable Fields menu does not appear on the left side of your screen, right click within the empty Pivot Table and select ‘Show Field List’.

Answering Questions with Pivot Tables

• Here a few examples of the many questions we can answer:

  • What was the average of of the GROSS (ticket sales) for each day of the week?

  • What was the minimum of the GROSS (ticket sales) for each day of the week?

  • What was the total gross in the last two months of the year?

  • Which day and month had the highest maximum GROSS?

Pivot Table Questions

• What was the average of of the GROSS (ticket sales) for each day of the week?

• Pivot tables allow you to drag and drop variables, and move data around efficiently

• There’s no one right way to answer a question but here is one solution:

1. Drag DAY to the Rows field.

2. Drag GROSS to Values column

3. Right click on a data value in the table and change ‘Summarize Values By’ to Average.

Lecture 4 In-class Exercise - Q2

What was the average gross sales on Fridays in 2024?

Round answer to closest million and just report that as a one or two digit answer, e.g. 6 million.

The average gross sales on Fridays was ____ million.

Lecture 4 In-class Exercise - Q3

Using the framework for the summary in Question 2, we can easily find the minimum or maximum for each weekday as well.

Which day had the had the lowest minimum GROSS in 2024?

Filtering Pivot Tables

• A useful quick tool for modifying a pivot table is a slicer.

• For example, we want to create a table of the maximum gross for each month in the Fall.

1. Drag MONTH to the Rows field.

2. Drag GROSS to the Values field.

3. Click on any cell in the pivot table so that the `PivotTable Analyze’ menu appears.

Filtering Pivot Tables Cont’d

Again we want to know the total GROSS for the last two months of 2024.

3. Click on Insert Slicer, select MONTH and click OK.

4. In the MONTH slicer menu, select Nov and Dec only.

Lecture 4 In-class Exercise - Q4

What is the approximate total GROSS for the last two months of the 2024?

Round to the closest million, e.g. 453 million.

___ million dollars

When you are done, clear the slicer filter, select the slicer menu, and delete it.

Slicing by Value

In this example, we want to find which day and month had the highest maximum GROSS?

1. Setup the Pivot Table as shown.

2. Click Insert Slicer and select GROSS and click OK.

3. Select the largest GROSS value only.

Lecture 4 In-class Exercise - Q5

Which day and month had the highest maximum GROSS?

The highest maximum gross occured on a ___(day of week) in ___ (month).

When you are done, clear the slicer filter, select the slicer menu, and delete it.

Counts and Percents in Pivot Tables

• In HW 2 you will examine 3 different sets of data about diamonds.

• The next two examples are similar to Question 3 and Question 4 in HW 2.

• The point of these examples is to show you how to

  • count the number of observations by categories.

  • convert counts to percentages.

  • make simple changes to answer different questions.

Counts and Percents Cont’d

We want to know how many diamonds in this dataset are classified as Colorless with Slight Inclusions.

1. Convert the Diamonds Data (Worksheet 2) to a table (Insert > Table)

2. Create a pivot table as shown

   • Note that other text columns would also work in the Values field

3. Count shows the number of observations in each Color/Clarity combination.

Lecture 4 In-class Exercise - Q6

How many diamonds in this dataset are classified as Colorless with Slight Inclusions?

Counts to Percents

• After creating a table of counts, we can convert counts to other useful values to answer questions.

• There are multiple ways to these same options.

• One simple way:

  1. Right-Click on any value within Pivot Table

  2. Select ‘Show Values As’

  3. Select option to answer your question

• In the example table, we select % of Column Total to answer this question because of how it is oriented.

Question: What percent of Colorless Diamonds have Slight Inclusions? (Similar to Questions 3 and 4 in HW 2)

Lecture 4 In-class Exercise - Q7

How many diamonds in this dataset are classified as Colorless with Slight Inclusions?

Changing Percents from Column to Row

• We can easily convert percentages without changing the pivot table.

• We want want to know what percent of diamonds with Slight Inclusions are Near colorless.

1. Right-click on any value in the table.

2. In ‘Show Values As’ change % of Column Total to % of Row Total

3. Examine the table to confirm that it is correctly set up to answer our question.

• Note: In HW 2, you can make this same quick change in Show Value As to answer Question 4 after completing Question 3.

Lecture 4 In-class Exercise - Q8

What percent of diamonds with slight inclusions are Near colorless?

Demonstrations

HW 2 Demo

A short demo of some key aspects of HW Assignment 2

R/Rstudio Demo using Posit Cloud

• Preview of the R and RStudio environment using Posit Cloud.

• This requires that you create free a Posit Cloud account.

Key Points from Today

• Pivot Tables

  • Very effective for summarizing categorical data
  • Efficient once you get used to using them

• Pivot Tables with Slicers

  • Useful for ‘On the Fly’ subsets and toggling between groups

• Caution

  • Sometimes filtering within the table and then filtering using slicers in a pivot table can cause conflicts. Use one or the other.
  • When in doubt, clear all previous filters and clear all fields on the Pivot Table.
  • Resetting the Pivot Table is the quickest way to resolve an error.

HW 2 is due Monday, 1/27/25

To submit an Engagement Question or Comment about material from Lecture 4: Submit it by midnight today (day of lecture).