By following the method used in the Positive And Negative worksheet, you can easily break down
© 2016 Microsoft Corporation. All Rights Reserved. Page 26 2/27/2017 a company’s revenue into its cost components (including profit). See the worksheet Profitability Waterfall and Figure 52-34. F52xx34 FIGURE 52-34 A profitability waterfall chart. F52xx34: This figure shows a waterfall chart that decomposes my $450,000 in revenue into its costs and profit components. How can I use the GETPIVOT data function and Excel’s table feature to create dynamic dashboards?
© 2016 Microsoft Corporation. All Rights Reserved. Page 27 2/27/2017 F52xx35 FIGURE 52-35 The sales data. F52xx35: This figure shows the source sales data for your dynamic dashboards. I now create a PivotTable by clicking the Insert tab and then PivotTable in the Tables group. In the PivotTable Fields pane, I drag the Week field to the Rows area, Store and Category to the Columns area, and Revenue to the Values area. This PivotTable (shown in Figure 52-36) summarizes the weekly sales for each category in each store. F52xx36 FIGURE 52-36 A PivotTable summarizing the category sales by store. F52xx36: This PivotTable shows the weekly category sales summarized by store.
© 2016 Microsoft Corporation. All Rights Reserved. Page 28 2/27/2017 Now I am ready to use the GETPIVOTDATA function to extract the data needed to create the charts I want. To begin, I create a drop-down list (see Chapter 40) in cell AG8 (still in the Model worksheet), which can be used to select a store. Then I create check boxes (see Chapter 27) for each category that can be used to control the range AH9:AK9. These cells control which categories appear in my chart. Then I copy from AH11 to the range AH11:AK24 the formula =IF(AH$9=FALSE,NA(),IFERROR(GETPIVOTDATA(“Revenue”,$I$11,”WEEK”,$AG11,”Category”,AH $10,”Store”,$AG$8),” “)). If I select the check box for a category, this formula extracts th e weekly sales for the category; if the category is unchecked, then an #N/A is entered in the cell. Also, if 0 sales occurred, the IFERROR portion of the formulas ensures that sales of 0 are entered. Next, I make the range AG10:AK24 a table so that any charts based on this range will update automatically to include the new data. Figure 52-37 shows how the source data for my chart appears if I want to
You've reached the end of your free preview.
Want to read all 39 pages?
- Summer '16
- naveen rathi
- Microsoft Corporation