When to make use of Calculated Columns and Calculated Fields
When first studying find out how to use Energy Pivot, most customers uncover the actual energy is in aggregating or calculating a consequence not directly. In case your information has a column with numeric values, you may simply combination it by choosing it in a PivotTable or Energy View Subject Checklist. By nature, as a result of it is numeric, it’s going to mechanically be summed, averaged, counted, or no matter sort of aggregation you choose. This is named an implicit measure. Implicit measures are nice for fast and straightforward aggregation, however they’ve limits, and people limits can virtually all the time be overcome with express measures and calculated columns.
Let’s first take a look at an instance the place we use a calculated column so as to add a brand new textual content worth for every row in a desk named Product. Every row within the Product desk incorporates all kinds of details about every product we promote. Now we have columns for Product Title, Colour, Dimension, Seller Worth, and so on.. Now we have one other associated desk named Product Class that incorporates a column ProductCategoryName. What we wish is for every product within the Product desk to incorporate the product class title from the Product Class desk. In our Product desk, we will create a calculated column named Product Class like this:
Our new Product Class system makes use of the RELATED DAX perform to get values from the ProductCategoryName column within the associated Product Class desk after which enters these values for every product (every row) within the Product desk.
This can be a nice instance of how we will use a calculated column so as to add a set worth for every row that we will use later within the ROWS, COLUMNS, or FILTERS space of PivotTable or in a Energy View report.
Let’s create one other instance the place we wish to calculate a revenue margin for our product classes. This can be a widespread situation, even in quite a lot of tutorials. Now we have a Gross sales desk in our information mannequin that has transaction information, and there’s a relationship between the Gross sales desk and the Product Class desk. Within the Gross sales desk, we now have a column that has gross sales quantities and one other column that has prices.
We are able to create a calculated column that calculates a revenue quantity for every row by subtracting values within the COGS column from values within the SalesAmount column, like this:
Now, we will create a PivotTable and drag the Product Class subject to COLUMNS, and our new Revenue subject into the VALUES space (a column in a desk in PowerPivot is a Subject within the PivotTable Subject Checklist). The result’s an implicit measure named Sum of Revenue. It is an aggregated quantity of values from the revenue column for every of the totally different product classes. Our consequence appears to be like like this:
On this case, Revenue solely is sensible as a subject in VALUES. If we have been to place Revenue within the COLUMNS space, our PivotTable would appear to be this:
Our Revenue subject does not present any helpful data when it is positioned in COLUMNS, ROWS, or FILTERS areas. It solely is sensible as an aggregated worth within the VALUES space.
What we have accomplished is create a column named Revenue that calculates a revenue margin for every row within the Gross sales desk. We then added Revenue to the VALUES space of our PivotTable, mechanically creating an implicit measure, the place a result’s calculated for every of the product classes. When you’re considering we actually calculated revenue for our product classes twice, you’re right. We first calculated a revenue for every row within the Gross sales desk, and we then added Revenue to the VALUES space the place it was aggregated for every of the product classes. When you’re additionally considering we did not actually need to create the Revenue calculated column, you’re additionally right. However, how then will we calculate our revenue with out making a Revenue calculated column?
Revenue, would actually be higher calculated as an express measure.
For now, we’ll go away our Revenue calculated column within the Gross sales desk and Product Class in COLUMNS and Revenue in VALUES of our PivotTable, to match our outcomes.
Within the calculation space of our Gross sales desk, we’ll create a measure named Whole Revenue(to keep away from naming conflicts). Ultimately, it’s going to yield the identical outcomes as what we did earlier than, however with no Revenue calculated column.
First, within the Gross sales desk, we choose the SalesAmount column after which click on AutoSum to create an express Sum of SalesAmountmeasure. Bear in mind, an express measure is one we create within the calculation space of a desk in Energy Pivot. We do the identical for the COGS column. We’ll rename these Whole SalesAmount and Whole COGS to make them simpler to establish.
Then we create one other measure with this system:
Whole Revenue:=[ Whole SalesAmount] – [Whole COGS]
Observe: We may additionally write our system as Whole Revenue:=SUM([SalesAmount]) – SUM([COGS]), however by creating separate Whole SalesAmount and Whole COGS measures, we will use them in our PivotTable too, and we will use them as arguments in all kinds of different measure formulation.
After altering our new Whole Revenue measure’s format to foreign money, we will add it to our PivotTable.
You’ll be able to see our new Whole Revenue measure returns the identical outcomes as making a Revenue calculated column after which putting it in VALUES. The distinction is our Whole Revenue measure is much extra environment friendly and makes our information mannequin cleaner and leaner as a result of we’re calculating on the time and just for the fields we choose for our PivotTable. We do not actually need that Revenue calculated column in any case.
Why is that this final half essential? Calculated columns add information to the information mannequin, and information takes up reminiscence. If we refresh the information mannequin, processing assets are additionally wanted to recalculate all the values within the Revenue column. We do not actually need to take up assets like this as a result of we actually wish to calculate our revenue after we choose the fields we wish Revenue for within the PivotTable, like product classes, area, or by dates.
Let us take a look at one other instance. One the place a calculated column creates outcomes that at the beginning look look right, however….
On this instance, we wish to calculate gross sales quantities as a share of complete gross sales. We create a calculated column named % of Gross sales in our Gross sales desk, like this:
Our system states: For every row within the Gross sales desk, divide the quantity within the SalesAmount column by the SUM complete of all quantities within the SalesAmount column.
If we create a PivotTable and add Product Class to COLUMNS and choose our new % of Gross sales column to place it into VALUES, we get a sum complete of % of Gross sales for every of our product classes.
Okay. This appears to be like good to date. However, let’s add a slicer. We add Calendar 12 months after which choose a 12 months. On this case, we choose 2007. That is what we get.
At first look, this would possibly nonetheless seem right. However, our percentages ought to actually complete 100%, as a result of we wish to know share of complete gross sales for every of our product classes for 2007. So what went mistaken?
Our % of Gross sales column calculated a % for every row that’s the worth within the SalesAmount column divided by the sum complete of all values within the SalesAmount column. Values in a calculated column are mounted. They’re an immutable consequence for every row within the desk. After we added % of Gross sales to our PivotTable it was aggregated as a sum of all values within the SalesAmount column. That sum of all values within the % of Gross sales column will all the time be 100%.
Tip: Be sure you learn Context in DAX Formulation. It gives understanding of row stage context and filter context, which is what we’re describing right here.
We are able to delete our % of Gross sales calculated column as a result of it is not going to assist us. As a substitute, We will create a measure that accurately calculates our % of complete gross sales, no matter any filters or slicers utilized.
Bear in mind the TotalSalesAmount measure we created earlier, the one which merely sums the SalesAmount column? We used it as an argument in our Whole Revenue measure, and we’ll use it once more as an argument in our new calculated subject.
Tip: Creating express measures like Whole SalesAmount and Whole COGS should not solely helpful themselves in a PivotTable or report, however they’re additionally helpful as arguments in different measures once you want the consequence as an argument. This makes your formulation extra environment friendly and simpler to learn. That is good information modeling observe.
We create a brand new measure with the next system:
% of Whole Gross sales:=([Total SalesAmount]) / CALCULATE([Total SalesAmount], ALLSELECTED())
This system states: Divide the consequence from Whole SalesAmount by the sum complete of SalesAmount with none column or row filters aside from these outlined within the PivotTable.
Tip: Be sure you examine CALCULATE and ALLSELECTED features within the DAX Reference.
Now, if we add our new % of Whole Gross sales to the PivotTable, we get:
That appears a greater. Now our % of Whole Gross sales for every product class is calculated as a share of complete gross sales for the 2007 12 months. If we choose a special 12 months, or a couple of 12 months within the CalendarYear slicer, we get new percentages for our product classes, however our grand complete continues to be 100%. We are able to add different slicers and filters too. Our % of Whole Gross sales measure will all the time produce a share of complete gross sales no matter any slicers or filters utilized. With measures, the result’s all the time calculated in keeping with the context decided by the fields in COLUMNS and ROWS, and by any filters or slicers which can be utilized. That is the ability of measures.
Listed below are just a few pointers that will help you when deciding whether or not or not a calculated column or a measure is correct for a selected calculation want:
Use calculated columns
If you would like your new information to look on ROWS, COLUMNS, or in FILTERS in a PivotTable, or on an AXIS, LEGEND, or, TILE BY in a Energy View visualization, you could use a calculated column. Similar to common columns of knowledge, calculated columns can be utilized as a subject in any space, and if they’re numeric they are often aggregated in VALUES too.
If you would like your new information to be a set worth for the row. For instance, you might have a date desk with a column of dates, and also you need one other column that incorporates simply the variety of the month. You’ll be able to create a calculated column that calculates simply the month quantity from the dates within the Date column. For instance, =MONTH(‘Date'[Date]).
If you wish to add a textual content worth for every row to a desk, use a calculated column. Fields with textual content values can by no means be aggregated in VALUES. For instance, =FORMAT(‘Date'[Date],”mmmm”) offers us the month title for every date within the Date column within the Date desk.
If the results of your calculation will all the time be depending on the opposite fields you choose in a PivotTable.
If you might want to do extra advanced calculations, like calculate a depend based mostly on a filter of some kind, or calculate a year-over-year, or variance, use a calculated subject.
If you wish to hold the scale of your workbook to a minimal and maximize its efficiency, create as lots of your calculations as measures as potential. In lots of instances, your entire calculations could be measures, considerably decreasing workbook dimension and dashing up refresh time.
Hold in-mind, there’s nothing mistaken with creating calculated columns like we did with our Revenue column, after which aggregating it in a PivotTable or report. It is really a very good and straightforward solution to find out about and create your personal calculations. As your understanding of those two extraordinarily highly effective options of Energy Pivot grows, it would be best to create probably the most environment friendly and correct information mannequin you may. Hopefully what you’ve got discovered right here helps. There are another actually nice assets on the market that may enable you to too. Listed below are only a few: Context in DAX Formulation, Aggregations in Energy Pivot, and DAX Useful resource Middle. And, whereas it’s kind of extra superior, and directed in direction of accounting and finance professionals, the Revenue and Loss Knowledge Modeling and Evaluation with Microsoft Energy Pivot in Excel pattern is loaded with nice information modeling and system examples.