The logic of Power Pivot

Working with Power Pivot it isn’t hard. In fact it’s easier than Excel but the logic is totally different. But if you respect the next 3 rules, you will be able to build measures for your model

Rule 1: Structure of the data

When you want to create a measure, your data must be stored like in a database ; all the same data in only 1 column.

For instance, here is 2 presentation of the same data.

2 dimensional table
All the value in a single column

Even if the first presentation looks better because you can read easily your data, the second is the only one to present your data if you want to use Power Pivot.

With the second presentation, all the data for the calculation are in only one column and not 12 compared to the first presentation.

Rule 2 : Basic formulas are the best

When you work with Excel, COUNTIFS and SUMIFS are common functions to calculate with criteria. But when you work with Power Pivot, you must think more simple.

When you ask to an agregator to calculate your data, it’s the nature of the calculation that is the most important like SUM, COUNT, MIN, MAX, AVERAGE, …

In fact, this principle is already visible in Excel when you change the type of calculation in a Pivot Table.

With an agregator like Pivot Table, the basic calculations are the most important

Rule 3: Filters starts the measures

With Power Pivot, the name of  your formulas is a measure.

Measure SUM in Power Pivot

Once your have created your measures, you just have to simply drag it into a field in a pivot table to visualize the result.

Here is an example of the behavior of a measure in a pivot table. Each time you select a value in a slicer, this action give the order to the measure to be recalculated.

Leave a Reply

Your email address will not be published.