Build PowerPivot Data Model for Dynamics GP Sales Analysis

Power Pivot is an Excel 2013 add-in you can use to perform powerful data analysis and create sophisticated data models. With Power Pivot, you can mash up large volumes of data from various sources, perform information analysis rapidly, and share insights easily. (See a related article Upgrade PowerPivot Data Model to SSAS Tabular).

In both Excel and in Power Pivot, you can create a Data Model, a collection of tables with relationships. The data model you see in a workbook in Excel is the same data model you see in the Power Pivot window. Any data you import into Excel is available in Power Pivot, and vice versa.

The basic difference between Power Pivot and Excel is that you can create a much more sophisticated data model by working on it in the Power Pivot window.

Using PowerPivot and Excel, you can:

• Build an analytical database inside Excel by importing data from one or more data sources including SQL Server, Access, Excel and OData
• Build relationships among the data from multiple sources to analyze it as if it all originated from a single source
• Write advanced formulas with the Data Analysis Expressions (DAX) expression language to create calculated columns and fields
• Define Hierarchies to use everywhere in a workbook, including Power View
• Create KPIs to use in PivotTables and Power View reports
• Create Perspectives to limit the number of columns and tables your workbook consumers see
• Use various public and private data sources for analysis

In this article, I will describe the steps I used to build a data model to analyze Microsoft Dynamics GP Sales data.

The following screenshot shows the final PowerPivot data model in the diagram view.

The PowerPivot data model includes the following tables.

Table	Source	Additional information
SalesFact	SalesLineItems view	Filtered to retrieve only Invoice and Return line items. Signs reversed for Quantity, Cost Amount and Sales Amount for Return line items.
Customer	Customers view
Item	Items view
Site	IV40700 table
Salesperson	RM00301 table
Territory	RM00303 table
Calendar	Excel sheet containing dates and other related information (linked table)	Mark table as Date table for time based calculations Sort Month Name by Month (number) for correct sorting Hierarchy for Year, Quarter, Month and Day
Period	Pasted from Excel table	Used for time based calculation in DAX formula for: Current MTD QTD YTD PrevYear PrevYearMTD PrevYearQTD PrevYearYTD

Calculated Column and Fields

Calculated Column/Field	Formula	Additional information
Gross Margin	SalesFact[Sales Amount]-SalesFact[Cost Amount]	Calculated column. The rest are calculated fields.
Sales	SUM([Sales Amount])	Total sales based on calendar dates selection
SalesMTD	[Sales](DATESMTD(Calendar[Date]))	Total MTD sales based on the calendar dates selection
SalesQTD	[Sales](DATESQTD(Calendar[Date]))	Total QTD sales based on the calendar dates selection
SalesYTD	[Sales](DATESYTD(Calendar[Date]))	Total YTD sales based on the calendar dates selection
SalesPrevYearMTD	[Sales](DATEADD(DATESMTD(Calendar[Date]),-1,YEAR))	Total MTD sales last year based on the calendar dates selection
SalesPrevYearQTD	[Sales](DATEADD(DATESQTD(Calendar[Date]),-1,YEAR))	Total QTD sales last year based on the calendar dates selection
SalesPrevYearYTD	[Sales](DATEADD(DATESYTD(Calendar[Date]),-1,YEAR))	Total YTD sales last year based on the calendar dates selection
SalesPrevYear	[Sales](DATEADD(Calendar[Date],-1,YEAR))	Total sales last year based on calendar dates selection
PeriodSales	IF(HASONEVALUE(Period[Period]),  SWITCH(VALUES(Period[Period]),  "Current", [Sales],  "MTD", [Sales](DATESMTD(Calendar[Date])),  "QTD", [Sales](DATESQTD(Calendar[Date])),  "YTD", [Sales](DATESYTD(Calendar[Date])),  "PrevYear", [Sales](DATEADD(Calendar[Date],-1,YEAR)),  "PrevYearMTD", [Sales](DATEADD(DATESMTD(Calendar[Date]),-1,YEAR)),  "PrevYearQTD", [Sales](DATEADD(DATESQTD(Calendar[Date]),-1,YEAR)),  "PrevYearYTD", [Sales](DATEADD(DATESYTD(Calendar[Date]),-1,YEAR)),  BLANK()))	Total Sales for the calendar dates selection and period (Current, MTD, QTD, etc.). This field can be dragged to the Pivot Table or report along with dates and period to dynamically calculate the sales amount.
YOYGrowth	IF([SalesPrevYear], ([Sales] - [SalesPrevYear]) / [SalesPrevYear], BLANK())	Year over year growth This field has an associated KPI (Less than -10% as red, -10% to +10% as yellow and greater than +10% as green)
GrossMargin	SUM([Gross Margin Amount])	Total Gross Margin based on calendar dates selection
GrossMarginPct	IF([Sales], [GrossMargin]/[Sales], BLANK())	Gross Margin Percentage
Cost	SUM([Cost Amount])	Total Cost based on calendar dates selection

The following screenshot shows the partial contents of the Calendar table. This table is marked as Date table.

The following screenshot shows the contents of the Period table which is used in the DAX formula for PeriodSales (see above).

The purpose of this table can be illustrated with the following two Pivot Tables which are essentially the same. However, the second Pivot Table was created by simply dragging the Period field to the columns area and the PeriodSales calculated field in the values area. The first Pivot Table requires adding each field to the values area i.e. Sales, SalesPrevYear, SalesMTD, etc.

Here are a few sample reports created from the data model.

Top 10 Customers - Sales for current year, previous year and YOY growth

Top 10 Items - Sales for current year, previous year and YOY growth

Top 10 Items - Sales, Cost, Gross Margin and Gross Margin Percent

Sales for current year and previous year and YOY growth by Location

Sales for current year and previous year and YOY growth by Salesperson

Sales for current year and previous year and YOY growth by Territory

PowerView report – Sales by State

Here are a few other reports you can build using this model:

• Fastest/slowest growing products
• New customer additions this year/quarter, etc.

In addition to the above sample reports, there are many more possibilities for enhancing the data model for creating more insightful reports depending on the available data and your business needs. For example, if you are a retailer and maintain square footage for each location in the Location table, you can create a calculated field for Sales per Square Foot. Similarly, if you maintain the employee count for each location, you can create a calculated field and KPI for Sales per Employee or Gross Margin per Employee.
You may also combine data from other sources like CRM to create meaningful reports.