Power pivot (advanced derived) table includes the process of creating a report table very similar to the Pivot table , whose main advantage is aggregating / grouping data from the initial table containing more than a million rows (1,048,576 rows, meaning over 1 million rows).
Advantages of power pivot table
In order to notice the good reasons why it is worth mastering the advanced power pivot table, let’s take a look at the following overview of the tasks we usually do in excel.
Task 1.
Import data from various sources such as databases, public sources, spreadsheet formats, and text files.
Derived (pivot) table
Import 1048567 rows of the table into the workspace, the ability to change the data in the existing table.
Improved power pivot
Connection with various data sources, filtering, renaming columns and table names, without the possibility of changing the data, unlimited number of rows (technically up to 2.097 billion rows).
Task 2.
Linking data from different tables, creating hierarchies, groups of dimensions and success indicators (KPI).
PivotTable The
connection is made using the VLOOKUP function or relation in Data / Relationships , there is no hierarchy creation, there is grouping in the derived table (Rows and Columns pane), there is no creation of success indicators (KPI).
Improved power pivot table
Connection is done using Diagram view in power pivot plugin, can create hierarchical overview and group dimensions using DAX formulas in calculation columns and calculation fields and based on defined dimensions has the ability to create success indicators (KPI).
Task 3.
Making calculations using functions and formulas.
PivotTable
Use about 455 Excel formulas and the ability to use VBA plug-ins (Visual Basic for Applications) to create new functions.
Improved power pivot table
Using a special set of 259 DAX formulas (Data Analysis Expressions), there is no possibility of VBA programming of new user functions.
It differs in that it is based on the data contained in the Data Model.
Working steps:
- Preparation of data for loading into the model,
- Establishing relationships between tables,
- Application of DAX formulas in tables,
- Creating a pivot table from the PowerPivot plugin,
- Customize the display of data in the desired reporting form.
Data preparation
How long it will take to prepare the data that will be processed in the Power pivot add-on mostly depends on the state of the source where the data is. If we are talking about different formats, technological platform, organization of data on the preparation itself, you can read more in the text about the Power Query add-on.
When it comes to data in excel tables, I recommend that you transform “visual” tables into objects simply after selecting one cell, press Ctrl + T as in the following figure.
Do this for all tables that participate in your analysis, and be sure to assign them the appropriate table name to make processing easier and faster.
Data model
Now add each table to the data model by clicking the Add to Data Model button as in the following figure.
The model can handle a large amount of data.
Establishing relationships
The next step you need to take is to select Diagram View (far right) in the PowerPivot plugin on the Home toolbar .
You will get content similar to the following image.
Click on Data View to return to the tabular data view and select the table in which you will calculate the desired data.
Application of DAX formula
In our example, this would mean going to the Traffic table and adding the following formula in the new Add Column column.
= Turnover [pcs] * (1-Turnover [discount]) * RELATED (Price list [price_product])
Where we calculate the amount by multiplying the data from the KOM column (number of pieces) we reduce by the amount of the rebate of the RABAT column and call the price for a given product from the corresponding price list RELATED (Price list [product_price]) as in the following figure.
When we have completed all the necessary calculations, we can start creating a Pivot table by clicking on the PivotTable button from the Home bar in the Power Pivot plugin. as in the following figure.
We have now returned to Excel and can proceed to further customize the pivot table to bring it into the desired reporting form.
The prepared example can be download here.