The Power Pivot
Power Pivot comes with all versions of Excel starting from 2019; some earlier versions require download. Power Pivot comes as an add-in which you must activate to start using Power Pivot.
Go to File > Options > Add-ins > Manage select COM Add-ins press go, and check the box ‘Microsoft Power Pivot for Excel’
Now you have Power Pivot Menu on main Excel window ribbon.
Press Manage button to go to Power Pivot window.
In this window, we will do the database set-up and design. We will first do the ETL (Extract, Transform, Load) operation.
From Home > From Other Sources > select Text file to load our csv files and press Next button.
Browse for the OrderTable.csv file, make sure to check the ‘Use first row as column headers’ and press Finish.
It will take a short while to load all 2 million lines. Press Close.
Follow the same steps to load the other 4 tables of the database. Power Pivot will take you to data view after loading each table.
After loading all tables, go to Diagram View.
In the Diagram View create connections between tables; between Primary Keys to related Foreign Keys of other tables.
Simple drag and drop of one key in a tablet o related key in another table establishes the connection.
Drag and drop each table on the view to set the best view of the database schema. Then create all connections between tables. Below is the relationships between the tables.
After creating all relationships, we will go back to Data View to add some formulas to get our tables ready for analysis.
Setting these relationships makes the analysis possible on the data contained in all sheets. The 2 graphics below show 2 pivot tables displaying total units sold by each employee, before and after establishing the connections.
Right: The pivot table with relationships set performs the calculation for the number of products that each employee has sold
With the connections set, we can analyse the data that is already included in the tables. But we will need more for our analysis, we have to perform calculations such as revenue, total cost and profit, and also we must extract month from date information.
We can do all on the pivot tables by adding calculated columns or adjusting display for the month view, but the best way to perform and later improve the calculations is to work on the Data view.
We can add columns to any table or add formulas which their results will be displayed as columns in pivot tables. The formulas in Power Pivot called measures. They are written using DAX language.
The columns of a Power Pivot table are database table columns. All the cells in a column will have the same type of data. A formula entered into a column will spread to all cells in that column.
Excel is originally in tabular form and a cell-based spreadsheet tool. So each cell can have a different formula and different type of data. In Power Pivot we work with tables.
We add a column to orders table for month column:
=MONTH(OrdersTable[Order Date])
When we write this formula and press enter, the whole column gets the same formula. This formula reads the order date column and returns the month value. This is calculated for every row of the table.
Rename the column as month.
We will then add measures for the total units sold, total cost, total revenue and profit. These measures can be added on the Excel worksheet when the pivot tables are created, but having them on the data view is a more preferred way because some analysis can be done directly on this page.
When you enter the formulas for measures Power Pivot automatically names them as Maesure1, Measure2 and so on and you must rename them for ease of use later in the pivot tables.
Total Units Sold:=[Sum of Units Sold]
Cost:=SUMX(OrdersTable, [Units Sold]*RELATED(ProductsTable[Cost (£)]))
Revenue:=SUMX(OrdersTable, [Units Sold]*RELATED(ProductsTable[Price]))
Profit:=[Revenue]-[Cost]
Bottom: the measures are seen in the Calculation Area
These measures show the totals for the moment, but they work with the filtered context of the tables they are performing calculations. Recognise the filter buttons on the header of orders table; they are all set to default, show all.
We want to see the total sales results of product 17 in the month of April. We will set the filters for that.
We are ready to begin our analysis. We will answer the same questions with pivot tables.
Questions
1- What are monthly sales volumes (total units sold, revenue, profit)
2- Regional sales figures (total units sold, revenue, profit)
3- Sales by product (total units sold, revenue, profit)
4- List top 10 countries by revenue
5- Top 100 customers by profit
6- Report top 5 employees who made the highest profits
Q1- What are monthly sales volumes (total units sold, revenue, profit)
Press the Pivot Table button, select a new sheet; you’ll land on Excel into an empty pivot table.
Analyse the pivot table fields, you’ll see that all the tables in our Power Pivot are here.
Click on the arrow pointing the Orders Table to see its columns. As you can see besides the original table columns, there are the calculated column and the measures as columns.
For our first question, we will put the month in rows and Units Sold, Revenue and Profit under Values field as SUM. Format the pivot table totals as numbers (see The Pivot) and change the column headers as you wish. The first Pivot table is ready.
Q2- Regional sales figures (total units sold, revenue, profit)
For all other questions first, we will copy the monthly figures pivot table and change the Rows field.
For regional sales, put regions column from CountryTable to the Rows field.
Q3- Sales by product (total units sold, revenue, profit)
For sales by product, put product column from ProductsTable to the Rows field.
Q4- List top 10 countries by revenue
For the top 10 countries, put country column from Country Table to the Rows field. Then filter to display top 10 and sort with descending order on revenue (see The Pivot for instructions filter and sort).
Q5- Top 100 customers by profit
For the top 100 customers, put Customer Name column from Customers Table to the Rows field. Then filter to display top 100 and sort with descending order on profit column.
Q6- Report top 5 employees who made the highest profits
For the top 5 employees, put the Employee Name column from Employees Table to the Rows field. Then filter to display top 5 and sort with descending order on profit column.
The pivot tables are complete for the answers to our questions. For analysis, we can add a timeline to our spreadsheet.
Select and pivot table, from PivotTable Analyse menu, select Insert Timeline. In the timeline box select Quarters as period, set the timeline in a convenient place in the spreadsheet.
You have to set the connections of the timeline, as we did for slicers in The Pivot. Right-click on the timeline, select Report Connections and check all pivot tables in the Pivot sheet in the dialogue box.
Examine the results by playing the quarters.