Tutorial: Create your own measures in Power BI Desktop - Power BI (2023)

  • Article
  • 11 minutes to read

By using measures, you can create some of the most powerful data analysis solutions in Power BI Desktop. Measures help you by performing calculations on your data as you interact with your reports. This tutorial will guide you through understanding measures and creating your own basic measures in Power BI Desktop.

Prerequisites

  • This tutorial is intended for Power BI users already familiar with using Power BI Desktop to create more advanced models. You should already be familiar with using Get Data and Power Query Editor to import data, work with multiple related tables, and add fields to the report canvas. If you’re new to Power BI Desktop, be sure to check out Get Started with Power BI Desktop.

  • This tutorial uses the Contoso Sales Sample for Power BI Desktop file, which includes online sales data from the fictitious company, Contoso. Because this data is imported from a database, you can't connect to the datasource or view it in Power Query Editor. Download and extract the file on your computer.

Automatic measures

When Power BI Desktop creates a measure, it's most often created for you automatically. To see how Power BI Desktop creates a measure, follow these steps:

  1. In Power BI Desktop, select File > Open, browse to the Contoso Sales Sample for Power BI Desktop.pbix file, and then choose Open.

  2. In the Fields pane, expand the Sales table. Then, either select the check box next to the SalesAmount field or drag SalesAmount onto the report canvas.

    A new column chart visualization appears, showing the sum total of all values in the SalesAmount column of the Sales table.

    Tutorial: Create your own measures in Power BI Desktop - Power BI (1)

    Any field (column) in the Fields pane with a sigma icon Tutorial: Create your own measures in Power BI Desktop - Power BI (2) is numeric, and its values can be aggregated. Rather than display a table with many values (2,000,000 rows for SalesAmount), Power BI Desktop automatically creates and calculates a measure to aggregate the data if it detects a numeric datatype. Sum is the default aggregation for a numeric datatype, but you can easily apply different aggregations like average or count. Understanding aggregations is fundamental to understanding measures, because every measure performs some type of aggregation.

To change the chart aggregation, follow these steps:

  1. Select the SalesAmount visualization in the report canvas.

  2. In the Values area of the Visualizations pane, select the down arrow to the right of SalesAmount.

  3. From the menu that appears, select Average.

    The visualization changes to an average of all sales values in the SalesAmount field.

    Tutorial: Create your own measures in Power BI Desktop - Power BI (3)

Depending on the result you want, you can change the type of aggregation. However, not all types of aggregation apply to every numeric datatype. For example, for the SalesAmount field, Sum and Average are useful, and Minimum and Maximum have their place as well. However, Count doesn't make sense for the SalesAmount field, because while its values are numeric, they’re really currency.

Values calculated from measures change in response to your interactions with your report. For example, if you drag the RegionCountryName field from the Geography table onto your existing SalesAmount chart, it changes to show the average sales amounts for each country/region.

Tutorial: Create your own measures in Power BI Desktop - Power BI (4)

When the result of a measure changes because of an interaction with your report, you've affected your measure’s context. Every time you interact with your report visualizations, you're changing the context in which a measure calculates and displays its results.

Create and use your own measures

In most cases, Power BI Desktop automatically calculates and returns values according to the types of fields and aggregations you choose. However, in some cases you might want to create your own measures to perform more complex, unique calculations. With Power BI Desktop, you can create your own measures with the Data Analysis Expressions (DAX) formula language.

(Video) Simple Measures - (1.10) Ultimate Beginners Guide to Power BI

DAX formulas use many of the same functions, operators, and syntax as Excel formulas. However, DAX functions are designed to work with relational data and perform more dynamic calculations as you interact with your reports. There are over 200 DAX functions that do everything from simple aggregations like sum and average to more complex statistical and filtering functions. There are many resources to help you learn more about DAX. After you've finished this tutorial, see DAX basics in Power BI Desktop.

When you create your own measure, it's called a model measure, and it's added to the Fields list for the table you select. Some advantages of model measures are that you can name them whatever you want, making them more identifiable. You can use them as arguments in other DAX expressions, and you can make them perform complex calculations quickly.

Quick measures

Many common calculations are available as quick measures, which write the DAX formulas for you based on your inputs in a window. These quick, powerful calculations are also great for learning DAX or seeding your own customized measures.

Create a quick measure using one of these methods:

For more information about creating and using quick measures, see Use quick measures.

Create a measure

Suppose you want to analyze your net sales by subtracting discounts and returns from total sales amounts. For the context that exists in your visualization, you need a measure that subtracts the sum of DiscountAmount and ReturnAmount from the sum of SalesAmount. There's no field for Net Sales in the Fields list, but you have the building blocks to create your own measure to calculate net sales.

To create a measure, follow these steps:

  1. In the Fields pane, right-click the Sales table, or hover over the table and select More options (...).

  2. From the menu that appears, choose New measure.

    This action saves your new measure in the Sales table, where it's easy to find.

    Tutorial: Create your own measures in Power BI Desktop - Power BI (5)

    You can also create a new measure by selecting New Measure in the Calculations group on the Home tab of the Power BI Desktop ribbon.

    Tutorial: Create your own measures in Power BI Desktop - Power BI (6)

    Tip

    When you create a measure from the ribbon, you can create it in any of your tables, but it's easier to find if you create it where you plan to use it. In this case, select the Sales table first to make it active, and then choose New measure.

    The formula bar appears along the top of the report canvas, where you can rename your measure and enter a DAX formula.

    Tutorial: Create your own measures in Power BI Desktop - Power BI (7)

  3. By default, each new measure is named Measure. If you don’t rename it, new measures are named Measure 2, Measure 3, and so on. Because we want this measure to be more identifiable, highlight Measure in the formula bar, and then change it to Net Sales.

  4. Begin entering your formula. After the equals sign, start to type Sum. As you type, a drop-down suggestion list appears, showing all the DAX functions, beginning with the letters you type. Scroll down, if necessary, to select SUM from the list, and then press Enter.

    (Video) Create Measures Power BI

    Tutorial: Create your own measures in Power BI Desktop - Power BI (8)

    An opening parenthesis appears, along with a drop-down suggestion list of the available columns you can pass to the SUM function.

    Tutorial: Create your own measures in Power BI Desktop - Power BI (9)

  5. Expressions always appear between opening and closing parentheses. For this example, your expression contains a single argument to pass to the SUM function: the SalesAmount column. Begin typing SalesAmount until Sales(SalesAmount) is the only value left in the list.

    The column name preceded by the table name is called the fully qualified name of the column. Fully qualified column names make your formulas easier to read.

    Tutorial: Create your own measures in Power BI Desktop - Power BI (10)

  6. Select Sales[SalesAmount] from the list, and then enter a closing parenthesis.

    Tip

    Syntax errors are most often caused by a missing or misplaced closing parenthesis.

  7. Subtract the other two columns inside the formula:

    a. After the closing parenthesis for the first expression, type a space, a minus operator (-), and then another space.

    b. Enter another SUM function, and start typing DiscountAmount until you can choose the Sales[DiscountAmount] column as the argument. Add a closing parenthesis.

    c. Type a space, a minus operator, a space, another SUM function with Sales[ReturnAmount] as the argument, and then a closing parenthesis.

    Tutorial: Create your own measures in Power BI Desktop - Power BI (11)

  8. Press Enter or select Commit (checkmark icon) in the formula bar to complete and validate the formula.

    The validated Net Sales measure is now ready to use in the Sales table in the Fields pane.

    Tutorial: Create your own measures in Power BI Desktop - Power BI (12)

  9. If you run out of room for entering a formula or want it on separate lines, select the down arrow on the right side of the formula bar to provide more space.

    The down arrow turns into an up arrow and a large box appears.

    Tutorial: Create your own measures in Power BI Desktop - Power BI (13)

  10. Separate parts of your formula by pressing Alt + Enter for separate lines, or pressing Tab to add tab spacing.

    (Video) Power BI - Organize Measures with Measure Table

    Tutorial: Create your own measures in Power BI Desktop - Power BI (14)

Use your measure in the report

Add your new Net Sales measure to the report canvas, and calculate net sales for whatever other fields you add to the report.

To look at net sales by country/region:

  1. Select the Net Sales measure from the Sales table, or drag it onto the report canvas.

  2. Select the RegionCountryName field from the Geography table, or drag it onto the Net Sales chart.

    Tutorial: Create your own measures in Power BI Desktop - Power BI (15)

  3. To see the difference between net sales and total sales by country/region, select the SalesAmount field or drag it onto the chart.

    Tutorial: Create your own measures in Power BI Desktop - Power BI (16)

    The chart now uses two measures: SalesAmount, which Power BI summed automatically, and the Net Sales measure, which you manually created. Each measure was calculated in the context of another field, RegionCountryName.

Use your measure with a slicer

Add a slicer to further filter net sales and sales amounts by calendar year:

  1. Select a blank area next to the chart. In the Visualizations pane, select the Table visualization.

    This action creates a blank table visualization on the report canvas.

    Tutorial: Create your own measures in Power BI Desktop - Power BI (17)

  2. Drag the Year field from the Calendar table onto the new blank table visualization.

    Because Year is a numeric field, Power BI Desktop sums up its values. This summation doesn’t work well as an aggregation; we'll address that in the next step.

    Tutorial: Create your own measures in Power BI Desktop - Power BI (18)

  3. In the Values box in the Visualizations pane, select the down arrow next to Year, and then choose Don't summarize from the list. The table now lists individual years.

    Tutorial: Create your own measures in Power BI Desktop - Power BI (19)

  4. Select the Slicer icon in the Visualizations pane to convert the table to a slicer. If the visualization displays a slider instead of a list, choose List from the down arrow in the slider.

    Tutorial: Create your own measures in Power BI Desktop - Power BI (20)

  5. Select any value in the Year slicer to filter the Net Sales and Sales Amount by RegionCountryName chart accordingly. The Net Sales and SalesAmount measures recalculate and display results in the context of the selected Year field.

    Tutorial: Create your own measures in Power BI Desktop - Power BI (21)

    (Video) NEW way to create measures in Power BI!

Use your measure in another measure

Suppose you want to find out which products have the highest net sales amount per unit sold. You'll need a measure that divides net sales by the quantity of units sold. Create a new measure that divides the result of your Net Sales measure by the sum of Sales[SalesQuantity].

  1. In the Fields pane, create a new measure named Net Sales per Unit in the Sales table.

  2. In the formula bar, begin typing Net Sales. The suggestion list shows what you can add. Select [Net Sales].

    Tutorial: Create your own measures in Power BI Desktop - Power BI (22)

  3. You can also reference measures by just typing an opening bracket ([). The suggestion list shows only measures to add to your formula.

    Tutorial: Create your own measures in Power BI Desktop - Power BI (23)

  4. Enter a space, a divide operator (/), another space, a SUM function, and then type Quantity. The suggestion list shows all the columns with Quantity in the name. Select Sales[SalesQuantity], type the closing parenthesis, and press ENTER or choose Commit (checkmark icon) to validate your formula.

    The resulting formula should appear as:

    Net Sales per Unit = [Net Sales] / SUM(Sales[SalesQuantity])

  5. Select the Net Sales per Unit measure from the Sales table, or drag it onto a blank area in the report canvas.

    The chart shows the net sales amount per unit over all products sold. This chart isn't informative; we'll address it in the next step.

    Tutorial: Create your own measures in Power BI Desktop - Power BI (24)

  6. For a different look, change the chart visualization type to Treemap.

    Tutorial: Create your own measures in Power BI Desktop - Power BI (25)

  7. Select the Product Category field, or drag it onto the treemap or the Group field of the Visualizations pane. Now you have some good info!

    Tutorial: Create your own measures in Power BI Desktop - Power BI (26)

  8. Try removing the ProductCategory field, and dragging the ProductName field onto the chart instead.

    Tutorial: Create your own measures in Power BI Desktop - Power BI (27)

    Ok, now we're just playing, but you have to admit that's cool! Experiment with other ways to filter and format the visualization.

What you've learned

Measures give you the power to get the insights you want from your data. You've learned how to create measures by using the formula bar, name them whatever makes most sense, and find and select the right formula elements by using the DAX suggestion lists. You've also been introduced to context, where the results of calculations in measures change according to other fields or other expressions in your formula.

Next steps

  • To learn more about Power BI Desktop quick measures, which provide many common measure calculations for you, see Use quick measures for common calculations.

  • If you want to take a deeper dive into DAX formulas and create some more advanced measures, see Learn DAX basics in Power BI Desktop. This article focuses on fundamental concepts in DAX, such as syntax, functions, and a more thorough understanding of context.

    (Video) How to Use Quick Measures in Power BI Desktop

  • Be sure to add the Data Analysis Expressions (DAX) Reference to your favorites. This reference is where you'll find detailed info on DAX syntax, operators, and over 200 DAX functions.

FAQs

How do I create a new measure in Power BI desktop? ›

From a table in the Fields pane, right-click or select More options (...), and then choose New quick measure from the list. Under Calculations in the Home tab of the Power BI Desktop ribbon, select New Quick Measure.

How do you create a calculated column and measure? ›

We can create a calculated column to reference a measure value like this: Column1= <measure name>. But you need to note the calculated column values are calculated based on table context so it's fixed. To do further calculation, you can use measure directly without creating additional calculated column.

What is simple measures? ›

The simple measurement is the one that uses only one type of unit.

How do I use quick measure? ›

To use Quick Measure, just point your smartphone at the object you're measuring, drop tags and your phone will quickly calculate distances and dimensions. It's a great tool for anyone who wants to get quick estimates of indoor or outdoor spaces without the need for extra tools.

What is the difference between calculated columns and measures? ›

a calculated column belongs to a single table, while a measure belongs to the whole data model. A calculated column is evaluated in a row context (row by row, like in an excel table), while a measure is evaluated in the filter context.

What is a measure in Power Query? ›

Measures, also known as measures in Power Pivot in Excel 2013, are calculations used in data analysis. Examples commonly found in business reports include sums, averages, minimum or maximum values, counts, or more advanced calculations that you create using a Data Analysis Expressions (DAX) formula.

Can we create sets using measures? ›

You cannot create a set from measures, as far as I know, but you can use a parameter to do this. Abigail, You can re-characterize a measure as a dimension (just drag it from Measures to Dimensions -- or, if you want to keep them as measures, create duplicate fields for A and B and then use those as dimensions).

What are the four types of measures? ›

You can see there are four different types of measurement scales (nominal, ordinal, interval and ratio). Each of the four scales, respectively, typically provides more information about the variables being measured than those preceding it.

What are the three types of measures? ›

Three Types of Measures

Use a balanced set of measures for all improvement efforts: outcomes measures, process measures, and balancing measures.

How do you write measures? ›

The first number is the width (W), the second number is the depth (D), and the third number is the height (H). Another way to write the same measurements would be: 30″W x 16″D x 36″H. In this format, all of the dimensions are listed one after the other, separated by a space.

What is difference between DAX and Power Query? ›

Define relationships: Power Query knows no relations. In DAX, on the other hand, you can define and change relationships, but also enforce them using referential integrity. Relationship functions (hierarchies). DAX contains functions to return e.g. the cardinality of relations and records.

How do you create a calculated field? ›

Add a calculated field

Click the PivotTable. This displays the PivotTable Tools, adding the Analyze and Design tabs. On the Analyze tab, in the Calculations group, click Fields, Items, & Sets, and then click Calculated Field. In the Name box, type a name for the field.

What is a measure example? ›

A few of those examples are listed below. Measuring the body temperature using a thermometer. Measuring the weight of fruits and vegetables in a shop using a weighing machine. Measuring the height of a child to keep his/her growth record.

What is the meaning of one measure? ›

an exact amount, especially of alcohol: One unit of alcohol is equal to half a pint of beer or a standard measure of spirits. [ C ] US. a bar. More examples.

What is data measurement? ›

Sometimes called the level of measurement, it describes the nature of the values assigned to the variables in a data set. The term scale of measurement is derived from two keywords in statistics, namely; measurement and scale. Measurement is the process of recording observations collected as part of the research.

Is Quick Measure accurate? ›

The calculations used within Quick Measure assume that the ground is flat. It will not give an accurate reading if there is a significant slope between you and the object which you are measuring. This app will only measure distances up to 100m.

Is there an app that measures? ›

Moasure is one of the best measurement apps on Android and iOS and features a 300m/1000ft tape measure, ruler, protractor, and a goniometer. You can use Moasure to measure distances up to 300m/1000ft and angles over distances up to 30m/100ft.

Which is faster calculated column or measure? ›

Because measures are not stored in memory, they are generally faster, although it is important to recognize the tradeoff between utilizing in-memory storage or processing power in an instance where either a measure or a calculated column could be used.

What is difference between Power Query and Power Pivot? ›

Power Query is the recommended experience for importing data. Power Pivot is great for modeling the data you've imported. Use both to shape your data in Excel so you can explore and visualize it in PivotTables, PivotCharts, and Power BI.

What is the difference between M and DAX? ›

DAX is a formula language. M Language is a query language. DAX is more powerful than M Language. For example, DAX can create KPIs and measures, while M cannot do that yet (though it might come in the future).

What are the methods used to measure power? ›

The standard methods for measuring power are: measuring a non electric effect of power absorption, measuring an electric effect of power absorption, using the exponential or square-law characteristics of semiconductors, and applying Ohm's law, measuring current or voltage, knowing the impedance, and measuring current ...

What is a DAX measure? ›

A measure is a formula that is created specifically for use in a PivotTable (or PivotChart) that uses Power Pivot data. Measures can be based on standard aggregation functions, such as COUNT or SUM, or you can define your own formula by using DAX. A measure is used in the Values area of a PivotTable.

What are measures in a dataset? ›

Measures are quantifications — fields like order subtotal, quantity of items purchased, or duration spent on a specific page.

When we create a set for a measure where does it get placed? ›

Sets are computed before dimension filters and measure filters.

Can we use measure in DAX? ›

A measure is evaluated in the context of the cell evaluated in a report or in a DAX query, whereas a calculated column is computed at the row level within the table it belongs to. The context of the cell depends on user selections in the report or on the shape of the DAX query.

How do you create measures in Analysis Services? ›

Click on the column for which you want to create the measure, then click the Column menu, then point to AutoSum, and then click an aggregation type. The measure will be created automatically with a default name, followed by the formula in the first cell in the measure grid directly beneath the column.

How do you create a measure group? ›

In the Measures pane, right-click the top node to create a new measure group, or new measures, by specifying a source table, column, and aggregation type.

Can DAX be used to create calculated columns and measures? ›

Calculated columns use Data Analysis Expressions (DAX) formulas to define a column's values. This tool is useful for anything from putting together text values from a couple of different columns to calculating a numeric value from other values.

What does == mean in DAX? ›

The “strictly equal to” operator == returns TRUE when the two arguments have the same value or are both BLANK. A comparison between BLANK and any other value returns FALSE.

Should I use M or DAX? ›

So, what is the difference between M and DAX? In one sentence: M is used for data engineering (sometimes called data prep), and DAX for data analysis.

Can a DAX measure return a column? ›

DAX - Measure that returns specific columns based on filter selection.

What is the process of measure? ›

measurement, the process of associating numbers with physical quantities and phenomena. Measurement is fundamental to the sciences; to engineering, construction, and other technical fields; and to almost all everyday activities.

What are measures in data analysis? ›

A measure is a number marked in standard units. Specifically, this number should be a quantification of some observation.

What are the 3 types of measurement? ›

You can see there are four different types of measurement scales (nominal, ordinal, interval and ratio).

What is the difference between measures and a measure group? ›

In a cube, measures are grouped by their underlying fact tables into measure groups. Measure groups are used to associate dimensions with measures. Measure groups are also used for measures that have distinct count as their aggregation behavior.

How do you write a measure? ›

Units of measure can be written as symbols, words, or abbreviations. For basic units of measurement, use words: 25 pounds, 12 inches. For derived units of measure — ones formed using a calculation — use symbols: 38mph, 27ft/s2.

What is the difference between a measure and a calculated column? ›

a calculated column belongs to a single table, while a measure belongs to the whole data model. A calculated column is evaluated in a row context (row by row, like in an excel table), while a measure is evaluated in the filter context.

Is DAX same as Excel formula? ›

DAX formulas are very similar to Excel formulas. To create one, you type an equal sign, followed by a function name or expression, and any required values or arguments.

What is the difference between CALCULATE and Calculatetable in DAX? ›

CALCULATETABLE is identical to CALCULATE, except for the result: it returns a table instead of a scalar value.

Videos

1. Creating a Calculated Field and Measure in Power BI
(Vijay Perepa)
2. 📊 How to use Power BI DAX - Tutorial
(Kevin Stratvert)
3. Creating First DAX calculated Field in Power BI Desktop
(Data Science Tutorials)
4. Get started with DAX using QUICK MEASURES in Power BI
(How to Power BI)
5. Measures vs. calculated columns in DAX and Power BI
(SQLBI)
6. Power BI Tutorial From Beginner to Pro ⚡ Desktop to Dashboard in 60 Minutes ⏰
(Avi Singh - PowerBIPro)
Top Articles
Latest Posts
Article information

Author: Rev. Leonie Wyman

Last Updated: 12/24/2022

Views: 6618

Rating: 4.9 / 5 (59 voted)

Reviews: 90% of readers found this page helpful

Author information

Name: Rev. Leonie Wyman

Birthday: 1993-07-01

Address: Suite 763 6272 Lang Bypass, New Xochitlport, VT 72704-3308

Phone: +22014484519944

Job: Banking Officer

Hobby: Sailing, Gaming, Basketball, Calligraphy, Mycology, Astronomy, Juggling

Introduction: My name is Rev. Leonie Wyman, I am a colorful, tasty, splendid, fair, witty, gorgeous, splendid person who loves writing and wants to share my knowledge and understanding with you.