You have downloaded a simple sales report from ERP.

The report includes product data, region, sales representative, sales amount and margin.

You could easily create a Pivot table using the range A1:E28 as source data.

Another option is to create a table and add the table to data model.

Activate the range manually or control + A. Then select insert – table or press control + T.

Press ok. Check that My table has headers tick box is activated,  as the first row includes header information.

Layout changed a bit, when table was created based on the range.

Choose power pivot – add to data model.

Data is now in Power pivot data model.

Table name was changed to more descriptive Tr sales. Tr stands for transactions.

Create a pivot based on workbook’s data model. The traditional pivot would be used when selecting the first radio button, now we use workbook’s data model as source data for pivot.

Report the margin per sales rep based on data model.

The results.

This could have been done with normal Pivot, too.

However, both products and sales reps are just codes in the transaction table. To make report more informative, codes could be replaced by real names.

Master data table for sales reps. Table includes name, day for starting the employment and boss’s name.

Make the table by activating the area and pressing control + T. Then press ok. Select Power Pivot – add to data model. Table name was changed to md_rep.

Note that the data is imaginative.

Master data table for products include product name, size of the product and product division.

Table was made also from the product table. Table name is md_prod.

Relationships in data model. Tr sales-product and Tr sales-rep are foreign keys in transaction table. Corresponding primary keys in master data tables area md_rep-rep and md_prod-product.

Cardinality is many in transaction table and one in master data tables.

Now we can start actual reporting.

To see how two sales teams have been doing by product division. Remember to select all the tables, not only active ones.

If you pivot field selection looks different, press

and select Field section area section side-by-side.

A calculated column created to count the margin percentage.

It took more time to transfer the report and the data to data model than using the traditional pivot table. However, data model creates basis for more business intelligence type reporting. Once master data is in place, you can use those with other transaction tables. Data model gives you countless number of possibilities to understand the data. Only few examples were shown here.