Something Cool.

• Start with a clean copy of this data
• We need to make tables out of the three worksheets.
• Make sure to name the tables as you go, RawData, Currency, CCodes
• Now go to the data tab, Data tools workgroup.
• Notice that
  • Country on RawData matches Alpha2 on CCodes
• Select Relationships
  • 
  • Mark the two above relationships.
  • 
  • Note the primary key is the place where the code is "defined"
• If you try to link the currency to the Alphabetic code it complains.
• So we need a new table.
  • Copy columns B and C of Country Codes to G and H
  • Use Remove Duplicates on the Data Tools tab of the Data tab to create a unique list.
  • You probably have to remove the blank alphabetic code line (No universal currency)
  • Now create and name the table (CurCode)
• Go to the raw data table and insert a pivot table.
  • But please check Use this workbook's Data Model
  • 
• Notice that now we have all of the tables available in the Fields.
  • 
• SO I want to know, the number of projects in each state by the currency name (not three letter code)
  • Place the Currency from the CurCode table into the Rows area
  • Place the state from the RawData table in the Columns area
  • Place the name from the RawData table in the Values area.
    • Notice, that the values shows count of name.
    • This is because the name is text and that is all we can do with this.
  • 
  • 
• One quick side trip while we are here.
  • Drag the Name from the CCodes table down below Currency in the Rows
  • Notice the multi level breakdown.
  • 
  • 
• When using numeric fields in the Values area we have many more choices
  • Remove the Name field from the Rows area (to reduce the size of the final table)
  • Remove Count of name from the Values area
  • Drag backers from the RawData table to the Values area.
  • Notice it has decided to sum backers.
  • This might be nice, but probably not what we want here.
  • You have two ways to change this (at least)
    • On the Analyzespecial table for PivotTable Tools in the Active Field workgroup select the Field Settings menu
    • 
    • Select the dropdown menu on the Sum of backers in the Values area
    • 
  • In either case, you can now
    • Change the computation that is performed
    • Change the formatting (lower left corner)
    • Change the Thing that is shown (Show Value As tab)
  • Change this to be average backers, to two decimal places.
  • 
• Can you find the average goal (usd goal) by country by currency?
  • Please do this in a New pivot table (save the old one)
  • Please display this as currency.
  • 
• Save this workbook, we will use it for the next few sets of notes.