Measures are a cornerstone of dashboard development using Power BI. We’ll explain what measures are, why they’re important and highlight some of the most important ones to be aware of to get you started.
We’ll explain what measures are, their significance, how they differ from Excel formulas, and some considerations to keep in mind when deciding between whether to create a measure or transform data using Power Query.
Let’s jump in!
Table of Contents
What are Power BI Measures?
Measures in Power BI are DAX (Data Analysis Expression) formulas used to perform calculations on data. They are designed to aggregate data to enable users to view data at different levels of detail. As a report viewer drills into report detail the formulas automatically re-calculate making them well suited for in-depth analysis.
When report viewers interact with reports, applying filters, slicers and navigating between different levels of detail, Power BI measures automatically re-calculate and adjust their results based on user interactions.
Power BI measures differ from calculated columns, and transformations that occur within Power Query in that compute values row-by-row in a table, measures perform aggregate calculations across a table or multiple tables.
Some of primary benefits of Measures are:
- Dynamic Calculations – Measures are automatically e-calculated when data changes or when different views are request by report users with slicers and filters.
- Efficient Performance – Calculations are performed extremely fast and are optimized for use with larger datasets than other platforms, such as Microsoft Excel.
- Space Saving – Because DAX formulas can be used for simple to complex calculations, there is less need to perform calculations at the source data level, saving storage space and allowing simpler data models.
Compared to creating new columns or using the Power Query editor, Measures are more dynamic. You should still consider using Power Query or new columns when calculations have to be done at a row-by-row basis.
Now let’s take a look at how to create measures in Power BI.
How to Create Measures in Power BI
Measures are create from the Report View of Power BI. It’s the same screen that allows you to drag and drop visuals into a page. To create a New Measure, navigate to the Home tab of the Power BI Ribbon, and select “New Measure”. A formula bar will appear below the ribbon allowing you to type in a custom formula.
The screenshot below shows where the New Measure button is in Power BI. When you click on it a formula bar will appear below the ribbon that lets you type in formulas..
In the example below, a New Measure is being created. The formula bar has an X and a check mark on the left side to cancel or accept the formula. The down arrow to the right side of the formula bar will expand the formula bar to provide additional working space to write longer formulas.
After a new measure is created, it will be available in the Data Panel on the right side of Power BI to drag and drop into different visuals.
Tip: If you create a new measure and it’s in the incorrect table, you can select it and Change the Home Table from the Power BI ribbon to move it to another location.
Example of Creating a Power BI Measure
One of the most common uses of measures is to sum data, like total sales, expenses, or counts of items. Power BI also supports common aggregation methods that you may be used to seeing in Excel pivot tables: Sum, Average, Count, Min, Max etc.
The syntax for Power BI measures include several elements.
All Measures must have a Name, an Equal Sign, and a Formula.
The words to the left of the = sign will be the measure name. (This can be edited later by double clicking on the measure from the data panel)
The part to the right of the = sign is the formula, or the calculation you want to perform on the data.
Measure Syntax Example
MeasureName = SUM(TableName[ColumnName])
MeasureName– The name you assign to your measure.
SUM– DAX function for summing.
TableName– The name of the table that contains the column you want to sum.
ColumnName– The name of the column that contains the values to sum.
Note: Almost every calculation in Power BI is performed at the column level. Rather than referencing a column letter like you would in Excel, you must reference the column name surrounded by square brackets.
SUM Measure Example
Let’s say you have a table named ‘Sales’ and you want to calculate the total sales amount. Your table has a column named ‘SaleAmount’ that records each sale’s value.
Total Sales = SUM(Sales[SaleAmount])
In this example, ‘Total Sales’ is the name of the measure.
This measure will add up all the values in the ‘SaleAmount’ column of the ‘Sales’ table. When used in a report, ‘Total Sales’ will change its value based on other report elements like filters or slicers. For instance, if you filter your report to show only sales from 2022, ‘Total Sales’ will automatically recalculate to show the total sales for that year.
Utilizing Measures in Reports
After creating a measure, you can use it in various visualizations like bar charts, line charts, and tables by dragging the measure into the values area of your visualization, and it will display according to the current context of the report.
Tips for Writing Power BI Measures Quickly
Here are some tips to help you write Power BI Measures more quickly and efficiently to help you get started writing DAX measures like a Pro!
Let Power BI Auto-Complete Table and Column Names
Because everything has to be referenced at the column level, and Power BI data models can contain multiple tables you often have to tell Power BI which column from which sheet you want to work with.
Tip: When typing a formula, Power BI will suggest columns from your dataset. Rather than typing out a full formula, use the Tab key on your keyboard to automatically complete a section of the formula. You can use the up down arrows on your keyboard to select a specific column before pressing tab when multiple suggestions appear.
By utilizing auto-complete it will reduce the potential for errors, and you won’t have to worry about typing in single quotes and commas in the right places.
Split Measures into Smaller Pieces
Power BI lets you create measures made up of other measures. Instead of writing a longer formula with two SUM calculations, you can set them up separately then reference the previously created measures to keep formulas shorter and simpler.
For example, we calculated the Sum of Total Sales, and the Sum of Cost of Goods Sold separately. Then used the formula in the screenshot below to subtract Cost of Goods Sold from Total Sales to calculate Gross Profit.
Since the measures have already been aggregated, Power BI automatically puts the two formulas together while making it easier for others to read and manage.
Write Formulas on Multiple Rows
The example below is a more complicated formula that utilizes Power BI Variables to give names to different fields allowing you to quickly change them if needed, but also highlights that you can Press ALT + RETURN to go to a new line.
You can perform a similar method in Excel, but few people actually do it. By splitting your formulas into multiple rows it greatly improves the ability to read measure formulas.
Adding Comments to Formulas
Our final tip is for creating long complex formulas. You can add documentation, or comments directly into formulas by escaping the formula. Try typing — or // at the beginning of a row in the Power BI formula. The row won’t be counted as part of the formula allowing you to type in notes for future references.
Common Examples of Measures
Power BI has hundreds of DAX formulas available to work with which can be overwhelming if you’re just starting out. The following table highlights some of the most common and most useful formulas that you will find yourself needing to use.
Total Sales = SUM(Sales[Amount])
|Adds up all the numbers in a column.
Average Sales = AVERAGE(Sales[Amount])
|Calculates the average of a column’s values.
Filtered Total = CALCULATE(SUM(Sales[Amount]), Sales[Region] = "West")
|Changes the context in which a data expression is evaluated.
Regional Sales = CALCULATE(SUM(Sales[Amount]), FILTER(Sales, Sales[Region] = "East"))
|Applies a filter to a calculation.
Sale Month = MONTH(Sales[Date])
|Extracts the month from a date column.
Sales YTD = TOTALYTD(SUM(Sales[Amount]), Sales[Date])
|Calculates the year-to-date total of a column.
This is in no way a comprehensive list but meant as a starting point. Search engines are a great resource, but so are AI tools like ChatGPT that can write DAX formulas on your behalf. Microsoft is even integrating AI directly into Power BI to write DAX Formulas for you using Power BI Copilot.
Power BI Measures are a necessary part of building reports in Power BI. They can be overwhelming at times, with entire books dedicated to the topic of DAX formulas. Luckily, for most people who are not professional Power BI developers there are about a dozen or so formulas that you can learn and re-use over and over again similar to how you utilize Excel. You can get by with knowing the basics and a handful of formulas or dive into the world of complex features and formulas that look more like computer code.