Abstract: At the end of the previous tutorial, Incorporate Internet Data, and Set Power View Report Defaults, your Excel workbook had a complete Data Model, and was ready to create reports. In this tutorial, you create three different Power View reports, learn some tips along the way, and learn steps you can use to create many, many more.
We start where the previous tutorial left off. You can download the workbook from the end of the previous tutorial here.
The sections in this tutorial are the following:
At the end of this tutorial is a quiz you can take to test your learning.
This series uses data describing Olympic Medals, hosting countries, and various Olympic sporting events. The tutorials in this series are the following:
- Import Data into Excel 2013, and Create a Data Model
- Extend Data Model relationships using Excel 2013, Power Pivot, and DAX
- Create Map-based Power View Reports
- Incorporate Internet Data, and Set Power View Report Defaults
- Create Amazing Power View Reports - Part 1
- Create Amazing Power View Reports - Part 2
We suggest you go through them in order.
These tutorials use Excel 2013 with Power Pivot enabled. For more information on Excel 2013, click here. For guidance on enabling Power Pivot, click here.
Create Interactive Map Charts
Building reports in Power View is fun and easy. And the reports you create are interactive, allowing viewers of the report to click on data elements, and gain new insights into your data.
In this section, you build a new map chart and extend it, then you create bar charts and pie charts that present the data in interesting ways. Along the way, you add some new calculations to the Data Model so the reports look the way you want them. Let’s get started.
Create an Interactive Map Chart
In a previous tutorial, you created a basic map report to display the host cities for various editions of the Olympic Games. You decide there’s more interesting information available in this Data Model, and you want to see it in different ways. To begin, you want to create a new Power View map report, to display the count of medals won by each country or region, the medal type, and then, results by season.
- In Excel, select the Power View1 worksheet. Your sheet looks like the following screen, based on work completed in previous tutorials, and depending on where you click on the canvas.
Create a new Power View report by selecting POWER VIEW > Insert > Power View from the ribbon. A blank Power View report sheet is created. Rename the report to Map, by right-clicking the tab along the bottom, and selecting Rename from the menu that appears.
- Expand the Medals table in the Power View Fields, and select NOC_CountryRegion. To get information on medals, select the Medal field. The table Power View creates looks like the following screen.
Uh-oh. That’s not what you want. This report displays the type of medal won by each country or region, and you want the count of medals. In a previous tutorial, you used a DAX formula to determine the percentage of medals awarded, so you decide to use DAX to calculate the count of medals.
- In Power Pivot, on the Medals tab, select the cell below the Medal column. If there are no cells available below the columns, the Calculation Area might not be displayed. To display the Calculation Area, select Home > View > Calculation Area. Type the following DAX formula into the cell.
Note: You can create DAX formulas in any cell in the Calculation Area, and have the calculated field available from that table in client tools, such as Power View or PivotTables. Selecting the cell below the Medal field is just a good way to keep track of which column it calculates.
- Back in Excel, your Data Model is updated. In the Map worksheet, your Power View Fields now shows the Medal Count field. Since Medal Count is a calculated field, a small calculator icon is displayed next to the field. With the table you created in the previous step selected, unselect Medal, and select Medal Count. That’s better. Your screen now shows how many medals were won by each country or region, as shown in the following screen.
- Select DESIGN > Switch Visualization > Map to display the table as a map. Resize the map to make it larger. When you hover over any of the bubbles on the map, additional information appears, as shown in the following screen. Try it for yourself.
Create an Interactive Pie Chart in a Map
Now that you have the Map visualization, it’s time to make it more interesting. In the following steps, you change the bubbles into interactive pie charts, with just a few clicks.
- In Power View, from Power View Fields, drag Medal to the COLOR field. Now each bubble on the map becomes a pie chart, with each section sized based on the type of medal awarded. When you select the map, navigation tools appear that let you zoom in, or pan, as shown in the following picture.
- Hover over any of the pie charts. Information is displayed based on which slice you hover over. In the following screen, I hover over the red slice for Spain (ESP), and the visualization displays information about that portion of the bubble. Notice, too, that the bubble expands, to allow easier navigation to other slices.
You now have an interactive Map that allows you to zoom in, zoom out, and hover over data elements and get more information.
Notice the medals are ordered alphabetically, which is the default. You want the order to be the following: gold, silver, bronze. To do so, we need to sort Medal by another value.
Change the Sort Order of a Field
To change the sort order from the default, you need to associate the Medal field with another field, then sort by the associated field. There is no associated field at this point, but you can create one. There are a few steps involved in doing this, but once you complete them, you see how convenient and easy it is to make the Data Model behave the way you want.
- In Excel, insert a new worksheet after Population. Rename the sheet MedalValue. Type the following data into the MedalValue worksheet. You can also copy and paste it in.
- Format the data as a table (while in one of the cells, you can press Ctrl + A, then Ctrl + T to format the data as a table). Make sure you select My Table has headers in the Create Table window. Under DESIGN > Properties > Table Name, name the table MedalValue.
- Still in Excel, select Power Pivot > Tables > Add to Data Model to add the table to the Data Model.
- In Power Pivot, select Diagram View. Move the MedalValue table beside the Medals table. It’s okay to move other tables out of the way to make room. In the Medals table, drag the Medal field to the Medal field in the MedalValue table, and create a relationship. Select the line between those tables to highlight the relationship you created. Your Diagram View looks like the following screen.
- In Data View in Power Pivot, Select the Medals table. Scroll right and select the column titled Add Column. In the formula bar, type the following DAX formula.
The RELATED function returns a value from another table in the Data Model. You can only sort with columns found in the same table, so we need the MedalValue values to exist in the Medals table. The RELATED function lets us pull a MedalValue field into the Medals table. Rename the column Medal Value.
- Now we need to specify how Power View (and other client tools) should sort the Medal field. Select the Medal column, then select Home > Sort and Filter > Sort by Column. In the window that appears, select Medal Value as the column by which to sort Medal, as show in the following screen.
- Back in Excel, the new sort order is automatically reflected in the Map visualization you created earlier, as shown in the following screen.
Filter Visualizations using a Slicer
Now that you have a Map visualization of Olympic events, you want to filter the results based on season. You can do that easily with Slicers.
- In Power View, drag Season from the Medals table in Power View Fields onto the report canvas. A table is created with the Season fields. The table will likely overlap your map, so resize the map and move the Season table beside the map.
- With the Season table selected, select DESIGN > Slicer > Slicer from the ribbon. The Map can now be filtered by the Season slicer. Try it out. On the slicer, click Summer, then Winter. Notice how the Map immediately updates the map to reflect the selection you make.
- To clear the slicer and see all results, click the Clear filter icon that appears above the slicer table. It only appears when you hover over the visualization. The Clear filter icon looks like small eraser, as shown in the following screen.
Filter other Visualizations using the Map
Not only can you use slicers to filter the map, you can use the map to interactively filter other tables you include in the report.
- In Power View, from the Events table, drag Sport to the report canvas. A table called Sports is created. Drag Medal Count, from the Medals table, onto the Sports table as well. Your report looks like the following screen.
Note: If blanks appear in your data, and you want them removed, use the Filters pane. For example, you may want to remove blanks from Sports. If the Filters pane is collapsed, expand Filters, then select the Sports table and click the arrow beside Sports, select (All) to select all, then (Blank) to exclude blanks from the visualization.
- When you click on the pie charts in the map, the Sports table visualization is filtered based on your selection, and by any slicers that have been selected. Pie charts that are not selected are dimmed, as are slices of the same pie that are not selected. For example, click Winter in the Season slicer, then zoom in and click on the orange portion of Finland’s pie chart, the Sports table adjust automatically, and immediately. Finland has won 95 silver medals, with 11 of them in the Biathalon, 18 in Ice Hockey, and so on. The following screen shows the results.
Create an Interactive Bar Chart
But wait, there’s more. It might be interesting to see the distribution of medals, in the map visualization, based on the Sport table visualization. And rather than numbers, you want to see it as a bar chart.
- Select the Sport table visualization. From the ribbon, select DESIGN > Switch Visualization > Bar Chart > Clustered Bar. The Sport visualization becomes a bar chart.
- From the Season slicer, select Summer. Notice how the Sport bar chart changes to reflect the sports that are part of the Summer Olympic events.
- On the Sport bar chart, click the bar next to Fencing. Notice that the other bars in the chart dim, but remain visible. Also notice that on the Map visualization, the Pie Charts change to reflect only medals awarded for the selection in the Sport bar chart. Countries or regions that have not won medals in fencing are dimmed. The following screen shows how your Power View report looks.
- Click some of the other bars in Sports, and see how Power View immediately updates the Map and the Bar charts based on your selection.
To see these visualization as a video, view the following video:
Create Interactive Pie Charts
You saw how the map visualization can include pie charts, but you can also create interactive pie charts in Power View outside of maps. In this section, you learn how to create interactive pie charts, how to drill down into the data within each pie, and how to create slices. Let’s get started.
Create an Interactive Pie Chart
- Select the Map tab in Excel, then click POWER VIEW > Insert > Power View. A new tab is created. Rename the tab Pie.
- From Power View Fields, select Year from the Medals table. Power View sums the data, because they are numbers. To prevent this, click on the arrow beside Year in the FIELDS area, and select Count (Distinct), as shown in the following screen.
- Next, from Events, select Sport. A table is created on the report canvas. To change the visualization to a Pie Chart, select DESIGN > Switch Visualization > Other Chart > Pie. The following screen shows your pie chart.
- That pie chart is too busy. You decide you want to filter the results to only winter sports. First, click the arrow icon in the collapsed Filters pane. The Filters pane expands. Click VIEW on the Filters pane, so that any filters you drag there will apply to all reports in the view. Drag Season from the Medals table into the Filters pane. Select Winter from the Season filter, as shown in the following screen.
- As with the Map visualization, the pie chart is interactive. Hover over any slice of the pie, and information is displayed. Clicking on any slice, or on any color in the legend, highlights that sport. In the following screen you click Skating, and its legend information and pie chart slice are highlighted, while all other slices and legend items are dimmed.
To clear the highlight, click again on the selected slice, or click the blank area in the visualization.
That’s an interesting pie chart, and it was easy to create. But Power View can create more interesting pie charts by visualizing multiple levels of depth inherent in hierarchies.
Create a Drill-Down Pie Chart using Hierarchies
Let’s create another pie chart, and use a hierarchy that you created in a previous tutorial.
- In Power View Fields, in the Events table, click Sport to deselect it, and then click SDE. SDE is the Sport/Discipline/Event hierarchy you created in a previous tutorial.
- The pie chart appears unchanged, but double-click on the Skiing slice of the Pie chart, and Power View drills down into the next level of the hierarchy. Now, the pie chart is based on the disciplines within the Skiing sport. Notice that the legend now lists the disciplines under the sport of skiing, as show in the following screen.
Also notice the up arrow that appears to the left of the filter icon, in the upper-right corner of the visualization. Click on the up arrow to drill up in the hierarchy, which in this case displays a pie chart of Sports.
- Double-click on the Ski Jumping slice, and the pie chart drills down another level into the hierarchy, which is Events. The events for Ski Jumping are displayed, and the legend shows the chart is displaying Events, which is the lowest level in the hierarchy.
- To return to higher levels in the hierarchy, also called drilling up, click the up arrow as shown in the following screen. When the highest level in the hierarchy is displayed, the up arrow is no longer displayed.
Use Slices to Display More Detail
You can display more detail in pie charts.
- From the Medals table in Power View Fields, drag Gender to the SLICES area. Each slice in the pie chart is now sliced to reflect how the data is divided by Gender. Hover over any slice in the pie chart, and the information provided is displayed based on Gender.
- Drill down into Skiing, then into Alpine Skiing. Hover over the lower slice of the downhill event, and you see that women’s teams have participated in the downhill event on 16 occasions, as shown in the following screen.
Cross-Filter Bar Charts with other Visualizations
You can apply many different filters to pie charts. You can even use other visualizations to filter pie charts, with interaction possible on all charts in the view.
- Click on the Power View report canvas outside the pie chart. From Power View Fields, select Population from the Population table, and then select Country Name from the Population table, to display the country or region name
- Let’s filter the view to just a handful of countries or regions. To display the Filters area, you can either select Filters Area from the POWER VIEW ribbon, or you can click the Filters icon that appears when you hover over a visualization, near the upper left corner. The Filters Area selection in the ribbon, and the Filters icon, are shown in the following screen.
- In Filters, select only the following Country or Regions: Austria, Canada, Chile, Czech Republic, France, and Germany.
- Let’s turn this into a Clustered Bar Chart. On the ribbon, click DESIGN > Switch Visualization > Bar Chart > Clustered Bar Chart.
- Click on one of the bars in the Bar Chart. Notice how data in the pie chart updates based on your selection, highlighting the corresponding information. Click on Canada in the Bar Chart. Your report looks like the following screen.
Try clicking other selections, and see how the pie chart changes immediately with your selection.
- You can also drill down into the data, just as you did before. Double-click on either Skiing slice in the pie chart, then double-click on the Alpine Skiing discipline, and the pie chart responds by displaying all Events in the Alpine Skiing discipline. Filtering results by clicking on the bar chart works the same at this level of the hierarchy. Click on France, and the pie chart filters Event results for France, as shown in the following screen.
You can use many different types of visualizations to filter pie charts, and in Power View, they’re all interactive. And when you post reports on a Business Intelligence [term] site on SharePoint, your Power View reports remain interactive to anyone with permission to view them.
Create Interactive Bar and Column Charts
You saw how bar charts can be used to filter other visualizations, such as a pie chart. In this section, you learn how to create compelling bar and column charts that are interactive.
- Select POWER VIEW > Insert > Power View to create a new Power View Report. Rename the report Bar and Column.
- From the Medals table, choose Medal Count, then NOC_CountryRegion from the Medals table as well. From the ribbon, select DESIGN > Switch Visualization > Column Chart > Clustered Column. The visualization has too many entries, so let’s filter the view to show only countries or regions that won 200 or more medals. To do that, click the Filter icon, and then change the mode of the filter to Advanced, by clicking the right-arrow icon beside the eraser, as shown in the following screen.
- To make the chart more interesting, drag Medal from the Medals table onto the visualization.
- Next, include a slicer. Click on the report canvas outside the chart, select Medal from the Medals table, and then select DESIGN > Slicer from the ribbon. Your report looks like the following screen.
- That’s interesting. But you decide it would be better as a stacked column. Select DESIGN > Switch Visualization > Column Chart > Stacked Column. That looks better, and your report now looks like the following screen.
- Now let’s add another slicer, based on Season. Click a blank area on the report canvas, then select Season from the Medals table. Then select Slicer from the ribbon. When you click on Winter, you notice there are no entries – looks like no country or region won more than 200 medals in the Winter. In the Filters pane, click CHART, change the Medal Count filter to at least 30 medals, and you see the following results.
- This chart is interactive, too. In the Medal legend, click on Silver. The Silver medal results are highlighted, and the rest of the results in the table are dimmed, as shown in the following screen.
- Although this is a Column Chart, it’s easy to change it to a bar chart. Select DESIGN > Switch Visualization > Bar Chart > Stacked Bar from the ribbon. You get the same chart, turned on its side into a bar chart, as shown in the following screen. The bar chart is just as interactive as the other charts.
There are all sorts of ways to interact with these Bar and Column charts. Explore it yourself, and see.
Checkpoint and Quiz
Review what you learned
In this tutorial you learned how to create interactive map, pie, bar, and column charts, with just a few clicks to create each. You also learned how hierarchies can make your reports more interactive, and enable viewers of your reports to become engaged with the report and its depth.
You also learned to put multiple visualizations into one report, and how those visualizations can filter, interact, and build on one another.
In the next tutorial in this series, you create more amazing Power View reports… including reports that animate, and show how the Olympics data changed over time. It’s pretty cool and easy to build, and even more fun to watch the reactions of people who see the reports you build.
Here’s a link to the next tutorial, so you can try creating these reports for yourself:
Tutorial 6: Create Amazing Power View Reports - Part 2
Want to see how well you remember what you learned? Here’s your chance. The following quiz highlights features, capabilities, or requirements you learned about in this tutorial. At the bottom of the page, you’ll find the answers. Good luck!
Question 1: Which of the following is true about the sort ordering of values in a field?
A: To change the sort order of a field, you must associate the field with another field, then delete the original field.
B: You cannot change the sort order of a field, no matter what.
C: If you want to sort by an associated field, the associated field must be in the Data Model.
D: None of the above.
Question 2: What functionality does a slicer provide?
A: Slicers duplicate data from one field to another.
B: Slicers cut data in half, and keep half the data for themselves.
C: Slicers filter visualizations based on data in the slicer field or fields.
D: None of the above.
Question 3: When you use a hierarchy in a report visualization, which of the following becomes possible?
A: Nothing – you cannot use hierarchies in visualizations.
B: You can drill down through levels of the hierarchy, and the visualization automatically changes to reflect the current level of the hierarchy.
C: You can drill down through levels of a hierarchy, but you cannot drill up.
D: Both B and C.
Question 4: In the Filters pane, which of the following is correct?
A: Dragging a field into the CHART area of the Filters pane filters all reports on the Power View sheet.
B: Dragging a field into the VIEW area of the Filters pane filters all reports on the Power View sheet.
C: You must drag the same field into both the CHART and VIEW areas of the Filters pane to filter all visualizations in the Power View sheet.
D: You cannot drag fields onto the Filters pane.
- Correct answer: C
- Correct answer: C
- Correct answer: B
- Correct answer: B
Note Data and images in this tutorial series are based on the following:
- Olympics Dataset from Guardian News & Media Ltd.
- Flag images from CIA Factbook (cia.gov)
- Population data from The World Bank (worldbank.org)
- Olympic Sport Pictograms by Thadius856 and Parutakupiu