**Total Sales,**percentage

**Growth**, and ratio of

**Internet**and

**Reseller**sales using PivotTables in Excel 2013. The data is from the Ferrari PowerPivot book.

### The Dashboard

A dashboard is a report which displays final results in a compact form. To begin, open the Dashboard workbook from Chapter 1 of the PowerPivot book we have been using. You will observe that this particular dashboard displays**Total Sales**of the AdventureWorks company. The

**Total Sales**is arranged by country by region, along with percentage

**Growth**of sales, and the percentage of

**Internet**and

**Reseller**sales. With the summarized information in a compact report, the dashboard allows the reader to visualize patterns and to assess the data more comprehensively than viewing a single table.

Fig. 1: Dashboard of Total Sales, Growth of Sales, and Internet-Reseller Sales by Country |

- The regions within the
**Country**are geographically sliced. So if there is only one region, then that region is collapsed and removed, leaving only the**Country**. A PivotTable cannot handle slicing and removing for only specific rows. - Column headers slice the headings differently and include different sets of
**Years**, as with two years you can determine the growth percentage between the two. Also,**Internet**and**Resell**are from where the products were sold, different than years. Again, mixed slicing cannot be achieved with a PivotTable. - The data elements inside the cells are calculated differently. They vary from summation for
**Total Sales**, percentage of sales**Growth**, and ratio of**Internet**and**Reseller**sales. The elements in the dashboard are from different calculations, not available in a PivotTable. - The colors in the dashboard allows the viewer to assess quickly the patterns in the data. They are derived from conditional formatting, different formulas for different cells, and are unable to be created with a PivotTable.

### Creating the PivotTables

We start by querying the Data Model in the workbook with PivotTables. Because PivotTables have a dynamic quality, the PivotTables can change when the source data is updated with new data. For example, adding new entries or fields can change the positional coordinates of the specific cell in a PivotTable referenced by the Dashboard. Therefore, instead of stopping at PivotTables, Excel tables will be used to construct the Dashboard. By referencing Excel tables instead of PivotTables, there will be no need to change the formulas for the Dashboard every time the data is modified.The Dashboard worksheet already has the Data Model created. To add a PivotTable from the Data Model present in the workbook, click the

*Insert*tab and then the

*PivotTable*button as shown in Post 1.3. To access the Data Model, select

*Choose Connection*under

*Use an*

*external data source*and the Existing Connections window will appear. The Data Model in the workbook is under the

*Tables*tab as shown in Figure 2.

Fig. 2: Creating a PivotTable from a Data Model |

**Internet**PivotTable, select the fields

**SalesTerritoryGroup**,

**SalesTerritoyCountry**,

**SalesTerritoryRegion**,

**FiscalYear**, and

**SalesAmount**from the

**DimSalesTerritory**,

**DimTime**, and

**FactInternetSales**tables. Place the fields into the quadrants as shown below in Figure 3.

Fig. 3: PivotTable Fields for Internet Sales |

For the

**Resellers**PivotTable, you also select the same fields except the

**SalesAmount**field is from the

**FactResellerSales**table. The fields are placed in the same areas below the field selection area. Now you have created the PivotTables for Internet and Reseller Sales, depicted in Figures 4 and 5, respectively.

Fig. 4: Internet Sales PivotTable |

Fig. 5: Reseller Sales PivotTable |

**Converting PivotTables to Excel tables**

To convert the PivotTables to 'regular' Excel tables, use the

*Convert to Formulas*button under the

*OLAP Tools*drop down button located in the

*Analyze*tab. Do this for both

**Internet**

**Sales**and

**Resellers Sales**tables.

Fig. 6: Convert to Formulas Button |

After converting both the PivotTables into Excel tables, you know have tables you can use to reference for the Dashboard. The two finished Excel tables are shown in the Figures 7 and 8 below.

Fig. 7: Reseller Sales Table |

Fig. 8: Internet Sales Table. |

### Creating the Dashboard

Now that we have the two Excel tables, we can create the Dashboard, using standard Excel formulas and certain formatting for cells. Start by copying the row labels to the new empty worksheet which will contain the Dashboard. Next label the column headings. You can add color formatting to distinguish the labels from the data as needed.Fig. 9: Dashboard Labels |

**Total Sales**when adding Internet and Reseller sales.

Fig. 10: IF() formula for Total Sales in Europe in 2006 |

**Growth**columns, you can use the mathematical formula to find the difference between the two desired years and divide by the base year for the percentage.

Fig. 11: Formula for Total Sales Growth in 2007 from 2006 |

**Internet**by

**Total Sales**, you can calculate the percentage of sales from

**Internet**sales

**(and**

**Reseller**sales when you divide

**Reseller**sales by

**Total Sales**) in each year.

Fig. 12: Formula for Percentage |

*Conditional Formatting*button shown below in Figure 13.

Fig. 13: Conditional Formatting Button |

**Internet**and

**Reseller**sales in lean towards Resellers in North America, whereas in Europe they are distributed more evenly after the years.

Now you have created a Dashboard containing information on

**Total Sales**, percentage

**Growth**in Sales, and percentage of

**Internet**and

**Reseller**sales by year and Country-Region.

Thanks for reading,

Wayne

## No comments:

## Post a Comment