Dashboards in Power Pivot

1. Dashboards in Power Pivot

Hey there! This screencast will review how to create a cohesive dashboard using Power Pivot. Let’s say the manager of PowerPivot Gym has asked us to analyze which classes are hitting their attendance goals. We can visualize this data in a PivotTable and PivotChart. First, let’s get into the Power Pivot window. From here, we can add some dashboard templates to a new worksheet. Let’s choose the chart and table (horizontal) and put it into a new worksheet. Let’s call this worksheet “Dashboard”, and we’ll remove gridlines. Power Pivot inserted these two objects for us, which is a nice way to get started. First, let’s add the class name to our PivotTable, as well as attendance percentage and the goal. It’s hard to make sense of this data all at a glance. It would be great to visualize the Key Performance Indicator of Attendenace Goal in our PivotTable to help tell a story. Click on KPIs, then New KPI. At the top, we can see the KPI base field, which is the value on which the KPI will be based. Then, we need to define a target value. We can do this with an absolute value, which just takes a number, or we can choose a measure. We want to compare our attendance percentage to the average attendance goal. We need to define the status thresholds. Green should be anything that hits our goal at 100%, yellow should be between 99% and 90% of the goa, and anything below this will be red. We can select an icon style we like, and then click OK. Sometimes, Excel doesn’t load correctly at first. If this happens, just toggle on the KPI in the fields off and on. Nice! Next, let’s build our PivotChart. Let’s make this a simple column chart of attendance percentage and class names, and sort this so we can easily visualize our most popular classes. Great! Don’t forget to give the chart a title. There are also some great formatting tools available for us to use. Let’s add data labels using the chart elements. One key staple of a dashboard is the ability to drill into and filter data. Slicers are interactive visual filters that provide a user-friendly interface for slicing and dicing data. Under pivot table analyze, we can insert a slicer for Class Name from classes. Let’s move things around to make this look better. If we click on a class, it will filter for that class. We can click this checklist icon for a multiple select, and this icon clears the slicer. Let’s get this slicer to be interactive with our entire report by connecting it to our PivotTable. Go to the Slicer ribbon, and report connections. Then we’ll select our PivotTable on our Dashboard sheet and click OK. Now, it’s working on both visuals. Awesome! This is a pretty good start to an impactful dashboard. Now you’ve seen it, it’s your turn to give it a try!

2. Let's practice!

Create Your Free Account

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.