1. Creating calculated columns and measures
Now it’s time to learn how to create calculated columns and measures in Power BI.
We’ll be using a single table that contains transactional information about commodities traded every day from January 2000 to March 2022. You can see all available columns by clicking on the dropdown next to the table on the fields pane.
Let’s start by renaming the report page from Page 1 to Commodities.
First, we will start with creating our first calculated column - this can be created from inside the report or data view, by selecting our table and clicking New Column.
Our table commodities contains an Open price and a Close price but we currently do not see the trading difference for each day on each commodity. We create a new column called TradingChange followed by an equals sign. As we start to type Close, Power BI’s intellisense leaps into action and shows us a list of all columns and functions that match our text. From here we can select Close followed by a subtraction sign then Open. Now we can commit this new column by clicking the checkmark or pressing enter.
Our new column looks great! Now it's time to create our first measure.
As our reports develop, we will start to create a multitude of measures, which can easily get lost in our data model. It’s best practice to create a calculations table in our model that acts as a folder for all our measures. It doesn’t contain any actual data, but rather a placeholder for all measures. The naming convention will always start with an underscore so that it appears top of the fields pane.
As you can see, this has already been created for you and called Calculations. When you create a new measure make sure that you have selected this table.
We will create a new measure called MaxClosePrice which computes the highest closing price. For this measure, we will now use the MAX function which takes a column name - in this case, our Close column. We can now close this function with a parenthesis and commit.
We can now see our new measure in our fields pane under the Calculations table.
Now that we have our measure created - let’s visualize it.
Let’s select a line chart from the visualizations pane and pull in our MaxClosePrice and Date. We can see that the Max close price for commodities trended upwards till 2011 before taking a dip and then slowly improving up until 2022.
Another great feature to get familiar with in Power BI, is the drill-down function on charts - it enables you to go from a high-level view to a more granular level when a hierarchy in the data exists.
For example, our Date uses Power BI’s built-in date hierarchy. We can use the drill feature to select 2014 and drill into the quarters for that year. You can keep drilling till you get to the lowest level in this case, Day. Equally, we can use the drill-up feature to move back up the hierarchy.
Alternatively, you can drill down to the second level of the hierarchy, not just the selected year but all years to see how it changes at a quarter level. The selective drill-down doesn’t give you this option. To achieve this, you can use the fork-like button. We can now see a new view for MaxClosePrice.
Now it’s your turn, let’s use the knowledge you’ve learned in this screencast and apply it!
2. Let's practice!
Let's practice!