Sign in or start a free trial to avail of this feature.
2. Calculated Columns and Measures
DAX formulas are used to create columns and measures in the Excel data model. In this lesson, we will see both methods and discuss which to use in a given situation.
- Calculated columns are computed for each row and stored in the data model
- A calculated column is only computed once, when it is created
- Measures use aggregated data, and not data for individual rows
- Measures are calculated only when they are needed
- The formula is stored in the data model, but the result is not
When to use each
- Calculated columns are best when creating a categorical field, or a field that will be used in filters and slicers
- Measures should be used when you want to perform calculations on the entire dataset
- Alt H, PT, T – Create PivotTable (in Power Pivot window)
- Alt H, 9 – Decrease decimal places
In Power Pivot, DAX formulas are used to add data to the data model using either calculated columns or measures.
As you may remember, we briefly introduced calculated columns and measures in our previous courses on Power Pivot. In this lesson, we'll compare calculated columns and measures and discuss when you should use each of them.
As we look over the dataset, we can see that each row contains data on quantity and total sales for the transaction.
A common calculation we can perform with these fields is the Unit Price or the average price that each unit of a product is sold for. Comparing unit prices across products can give us an insight into which products are selling for high prices and which are selling for low prices.
Let's create a calculated column to represent the unit price.
We'll select the Add Column area of the Pharma Data table to create the column.
We'll call the column Unit Price and the formula will be Sales divided by Quantity.
Let's create a Pivot Table to see a practical application of this calculated columns.
We'll add the pivot table to the existing Sheet1.
We'll then expand the Pharma Data table, add product name to the rows area and unit price to the values area.
We want the average of unit price, not the sum, so we'll select Sum of Unit Price, Value Field Settings and change the calculation to Average.
We'll also select the number format and reduce the number of decimal places to make the table easier to read.
We now have our table of unit price by product.
We can see that Melacaine is the most expensive product followed by Mizalone and most products have an averaging of price below one.
However, these figures are misleading. The average unit price we see here is calculated per transaction.
This means that a sale of one unit is weighted equally with a sale of 100 units.
To get a true picture of unit price for each product, we need to remove this effect.
We can do this by calculating unit price as a measure. We'll go back to Power Pivot and select the calculation area.
While a column performs a calculation for every row, a measure performs one calculation for the whole dataset.
In the Formula bar, we'll enter the name Unit Price Measure, enter the Sum of Sales divided by the Sum of Quantity and press Enter.
Now the quantity of individual transactions is not considered when calculating the unit price.
Let's return to Excel and add this new measure to our pivot table. We'll also reduce the number of decimal places in this field.
We can clearly see that the column and measure produce different results.
For example, Mizalone has a much lower unit price than the calculated column led us to believe.
In this case, the measure gives us the correct number for calculating unit prices.
At this point, you may be wondering when you should use columns and when you should use measures.
Calculated columns are calculated once and have a fixed result, so they're often used for fields that will be used in filters or slicers.
You generally don't want filters to change with your data, so a calculated column is ideal. Measures are useful when you want to perform calculations using aggregate data like the sum of sales and sum of quantity we used here. There are also useful if you want to perform one calculation for various different values, such as the unit price measure.
Columns are stored in the data model while measures are only calculated when required such as when they're added to a pivot table. Therefore in order to make your model run more efficiently, you should generally use measures unless you have a clear reason to use a calculated column. This finishes our review of calculated columns and measures.