Using Multiple Charts and Tables When creating pivot tables and charts to

Using multiple charts and tables when creating pivot

This preview shows page 147 - 151 out of 309 pages.

Using Multiple Charts and Tables When creating pivot tables and charts to display and make sense of the data, you often want to create a dashboard that easily allows you to determine performance. You may be interested in sales performance, network performance, or assembly-line performance. Dashboards combine visual representations, such as key performance indicators (KPIs), graphs, and charts, into one holistic view of the process. Although they are not technically considered dashboard tools, you can create some very compelling data displays using Excel with Power Pivot tables and charts that can then be displayed and shared in SharePoint. When adding multiple charts and tables to a dashboard, you may want to link them together so they represent the same data in different ways. You also will probably want to control them with the same slicers so that they stay in sync. The easiest way to do this is to add them using the Insert tab and selecting PivotChart & PivotTable (see Figure 7-19 ). Figure 7-18. Selecting a chart type
CHAPTER 7 DATA ANALYSIS WITH PIVOT TABLES AND CHARTS 146 Adding the pivot chart and pivot table in this way creates a link between them so that when you add a field to one, it adds the same field to the other. Also when you add a slicer to the page, it automatically hooks up the slicer to both the pivot table and the pivot chart. Figure 7-20 shows a simple dashboard consisting of a linked pivot table, pivot chart and slicer. Figure 7-19. Adding a pivot chart and a related pivot table
CHAPTER 7 DATA ANALYSIS WITH PIVOT TABLES AND CHARTS 147 If you need to connect multiple charts together, open the Model Designer and select the PivotTable drop-down on the Home tab (see Figure 7-21 ). This also allows you to insert a flattened pivot table, which is useful for printing. Figure 7-20. Creating a simple dashboard in Excel Figure 7-21. Connecting multiple pivot charts
CHAPTER 7 DATA ANALYSIS WITH PIVOT TABLES AND CHARTS 148 Using Cube Functions Using the built-in PivotChart and PivotTable layouts in Excel allows you to create compelling dashboards and provide great interfaces for browsing the data. There are times, however, when you may find yourself frustrated with some of the limitations inherent with these structures. For example, you cannot insert your own columns inside the pivot table to create a custom calculation. You may also want to display the data in a non-tabular format for a customized report. This is where the Excel cube functions are really useful. The Excel cube functions allow you to connect directly to the Power Pivot data model without needing to use a pivot table. The cube functions are Excel functions (as opposed to DAX functions) and can be found on the Excel Formulas tab under the More Functions drop-down (see Figure 7-22 ).

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture