Pivot Tables, Intro

• Let's start with the Raw King's County House Sales again.
• A pivot table simplifies the analysis of some tabular data.
  • It works best for small to medium categorical data.
• Let's start by clicking anywhere in the data.
  • On the insert tab, select Pivot Table
  • The arguments for now will be good
  • Later we will select Add this data to the Data_Model but we don't need that now.
• insert the pivot table by clicking OK.
• As implied by the name, we will build a table.
  • A one dimensional table is easy, just drag the value you are interested in into the Columns or Rows area
  • Add add the item you want to analyze to the Values area.
• Let's explore how the condition of the house impacts the price.
  • Drag condition into the Rows area
    • Not all we might hope for, but we do have the five different conditions.
  • Now drag the price into the values area.
  • 
  • Reflect.
  • We should probably clean this up a bit.
    • First of all, we probably don't want the sum of price.
    • There are two ways to change this.
      • In the Values Box drop down the selection box and click on Value Field Settings
      • 
      • Or in the Analyze special design tab,
        • click on any entry in the table to select the name in the Active Field command group.
        • Select Field Settings
        • 
      • In either case, this will bring up the Value Field Settings dialog box.
      • 
      • The Summarize value field by computation is
        • A sum if the field is numeric
        • A count otherwise.
      • Change sum to
        • Count
        • Average
      • Notice the Number Format box in the bottom of the pop-up.
        • Change the format to be Currency.
      • Just to double check, drag the date field into the Values area as well
        • Note, it was sensible and decided just to count the date.
        • Try the waterfront field.
      • Remove those fields by dragging them out of the values box.
  • Let's add another dimension
    • Drag zipcode into the Columns box.
    • That doesn't fit as well as I wish, let's flip those.
      • Drag zipcode from Columns to Rows
      • Drag condition from Rows to Columns.
    • 
    • There might be something to tease out here...
    • Right click on an entry in the Grand Total column, select sort and select Highest To Lowest
    • Notice that zipcode 98039 seems to have
      • Really expensive houses.
      • And nothing in low condition.
    • Just for fun, let's add a count field,
  • Double click in any cell in this table.
    • Note the criteria for the cells you selected.
  • Given the amount of rain we have had lets add waterfront into the mix
    • Drag waterfront into the Filters area
    • Drop this down and click the box next to Select Multiple Items
    • 
    • Now select 1.
    • What are we looking at.
  • I think the view might also be a factor.
    • Let's add the view to the filters as well.
    • And add the count back in.
      • Just one house in condition 1 on the waterfront, but it has a great view.
      • Look at the number of houses with a view of 4.
  • OK, so I want an waterfront house, with an outstanding view but I have only $1,000,000 to pay, do I have any hope.
    • Click on the sort/filter dropdown on Row Labels
    • 
    • I want to filter the values in the tables (as opposed to the zip codes) so select Value Filters and Less Than...
    • 
    • I guess i really want is less than or equal to so change the drop down.
    • And insert 1,000,000 (ya right)
    • 
    • Well, there are some choices.
  • Those total rows are somewhat distracting so remove them
    • Right click on Grand Total and select Remove Grand total
    • Or on the Design tab in the Layout command group change Grand Totals.
    • Look at the other settings on this page.
  • Click in Count of Date
    • In the formula bar, change this to just be count.
    • Change Average of Price to be Average Price
    • Change 5 to be Great Condition
    • Change 4 to be Good Condition
    • Change 3 to be Average Condition
  • Use the filter dropdown on Condition to select only houses in condition 3-5
  • 
  • Not a bad table.
  • Let's now make the worst graph ever.
    • On the Analyze tab under Tools command group, select Pivot Chart.
    • We have two really different scale pieces of information, so the only thing that really makes sense is a combo chart
    • 
    • Let's Select
      • Clustered Column for all of the Count fields.
      • Put these on the secondary axis as well.
      • Line with Markers for all of the Price fields.
      • 
    • Clean it up and label it
      • Change the maximum on the left axis to be 1,000,000
      • Label each axis
      • Give a Chart title.
      • Click on the gray box and select Hide All Field Buttons on Chart
      • 
    •