Modeling data with Power Pivot

Getting ready

Follow the steps in the Importing data with Power Pivot recipe to get your Excel environment ready.

How to do it...

1. Click on the Manage icon from the Power Pivot menu button to bring up the data you just loaded.

   

2. Click on a blank cell to create a new measure called Count_of_Crashes, Count_of_Crashs:=COUNT([CASENUMBER]), and then press Enter.

3. You will now have a new measure added to your model.

   

4. Next switch to the Diagram View in the upper right corner to show the tables loaded in the model.

   

5. Now you need to create relationships between the tables to enable DAX calculations to work properly. Drag the LIGHT column from LIGHT_T to the LIGHT column in CRASH_DATA_T. Then do the same for WEATHER, from WEATHER_T to WEATHER in the CRASH_DATA_T table to create the relationships.

   

6. Next create a Pivot Table chart to show the data. Select PivotTable, then Two Charts (Horizontal), and then select New Worksheet on the next window.

7. Finally, you are ready to create...