How to create calculated columns in Excel Power Pivot

In a previous post, we looked at how to create calculated column in Excel Power Query. Now, let’s explore when and how to perform the same task in Power Pivot:

Calculating in Power Query versus Power Pivot

Power Query and Power Pivot were designed for distinct, complementary tasks. But there is some overlap: both, for example, can derive calculate columns. So when should you use which?

In Power Query, calculated columns are created during the data transformation process, whereas in Power Pivot, calculated columns are created in the data modeling stage.

As a general rule, you should create calculated columns in Power Query when you need to perform data transformations on a large dataset or when you need to manipulate data in a way that is not easily achieved through built-in functions. For instance, you might use Power Query to extract a specific part of a text string or perform complicated calculations based on multiple columns. Power Query is a data cleaning tool, and it excels at calculations that require extensive cleaning.

On the other hand, create calculated columns in Power Pivot when the data is already cleaned. For example, you might use Power Pivot to calculate the difference between two columns, or to find the percentage of a column that meets a certain criteria. Remember, Power Pivot is a tool specifically for data analysis and reporting… not cleaning.

While these rules of thumb are helpful, the best way to decide whether to create calculated columns in Power Query or Power Pivot is to experiment with both tools and see which one works best for your needs.

Example: Calculating profit margin

Once the orders table has been loaded to the data model, head to the Data View of Power Pivot. Let’s create a calculated column called Profit Margin. Type in the following formula, then hit Enter:

=orders[Profit]/orders[Sales]

Go ahead and load our Data Model to a PivotTable, dragging Segment to Rows and Average of Profit Margin to Values.

To confirm our data is being calculated correctly in the PivotTable, go ahead and drag Sum of Profit and Sum of Sales into Values as well. Calculating profit margin by hand, there appears to be a discrepancy in what was found in the PivotTable. What gives?

The issue is that our Profit Margin calculated column is a simple straight average of every single profit margin in the data. What’s really needed is to aggregate total profits, then total sales, then find that new margin. This requires dynamic, on-the-fly calculations just not possible with calculated columns. To properly calculate Profit Margin, we will need to employ DAX measures, which you can learn about in other blog posts.

For now, just remember that calculated columns in Power Pivot should not be used if there’s a chance that the user might attempt to aggregate the results.

Example B: Large order category

So, when wouldn’t the user want to aggregate? Creating a categorical variable is a good example. Here, we’re going to use the the SWITCH() function to code each order as small, medium or large:

=SWITCH(
    TRUE(),
    [quantity] < 3, "Small order",
    [quantity] < 6, "Medium order",
    "Large order"
)

To learn more about how SWITCH() works, check out my blog post on the function. Or, go ahead and create another PivotTable in your workbook. For example, we could find the total number of units and profits made based on each level of order quantity:

Even after choosing Power Pivot over Power Query to create calculated column, we need to check for aggregation-related issues.

Recap

Perhaps you’re wondering why to use the SWITCH() statement at all when similar conditional columns can be created in Power Query. Good question! Doing this work in Power Pivot still makes sense because its calculated column expression editor allows for more advanced and more efficient functions and syntax than Power Query.

Have you created calculated columns in Power Pivot before? What rules of thumb have you found helpful for when to build a column in Power Pivot versus Power Query, or when to create a calculated column versus measure? Let me know in the comments.