How to create a measure in Power BI

In this Power BI Tutorial, we will learn about how to create a measure in Power BI and also we will see various examples of how to use Power BI measures. Also, we will discuss these topics:

  • What is measure in Power BI?
  • Create measures in Power BI Desktop
  • Syntax for Power BI Measure
  • Automatic measures available on Power BI
  • Use measures in Power BI Desktop
  • Use Measure with a Slicer
  • Use Mesure in another measure
  • Edit Measure Power BI

What is Power BI Measure?

The measure is a calculated field in Power BI DAX. Measure performs a real-time calculation based on our interactions with data on our report.

It is used as an aggregation calculation such as sum, average, minimum, maximum, total, counts, or more.

We can create new measures to make our analysis more easily on Power BI Desktop. In this topic, we will learn how to create measures in Power BI Desktop with various examples.

In the Power BI desktop, measures are created and displayed in a Report view or Data view. When we create a measure it appears on the Fields list with a calculated icon. We can rename them and add them to a new or existing visualization.

Measures in Power BI
Measures in Power BI

Automatic measures available on Power BI?

When Power BI Desktop creates a measure, it’s most often created for us automatically. To see how Power BI creates automatic measures, follow these steps:

Step-1:

In Power BI Desktop, we load sample data based on orders details of Products.

Step-2:

Now we will create a visualization using the data from Field. For example, here we will create a column visual using sales.

Automatic measures in Power BI
Automatic measures in Power BI

We can see a sigma symbol beside the Sales, unit price, Profit, Order quantity & ID. It represents that the columns are numeric. So it can be aggregated.

Power BI Desktop automatically creates calculates a measure to aggregate the data if it detects as a numeric column. The sum is the default aggregation for a numeric datatype, but we can easily apply different types of aggregations like Average, Minimum, Maximum, Count, Variance, median, etc.

Step-3:

To change the chart aggregation, select the visualization that we previously created. In the Value area, click the down arrow to the right of Sales.

Automatic measures in Power BI
Automatic measures in Power BI

According to our requirement, we can change the type of aggregations. For example, if we change the measures from Sum to Average, then the chart will display like this:

Automatic Measures available in Power BI
Automatic Measures available in Power BI

This is how we can use automatic Measures available in Power BI.

Read Power Bi key influencers

Create a measure in Power BI Desktop

In Power BI Desktop, we can create our own Measure with the Data Analysis Expression (DAX). There are 200 DAX functions available in Power BI, ranging from simple aggregation such as SUM, Average to complex aggregations (i.e, statistical and filtering).

When we create our own measure, it is called a Model Measure. Let’s create a measure on our data by following these simple steps:

Step-1:

In Field Panel, right-click on Orders or click on the More options(…) > select New Measure.

Create New Measure in power BI
Create New Measure in Power BI

Also, we can create a measure from the Home tab > Calculation Pane > New measure.

Create New Measure in power BI
Create New Measure in Power BI

Step-2:

Click on New measure, it will create a measure where we have to put DAX expression. Also, we can rename it.

How to create measure in Power BI
How to create a measure in Power BI

Step-3:

Then press Enter. Now it will visible on the Field pane having a calculation icon.

How to create a Measure in Power BI
How to create a Measure in Power BI

This is How to create a Measure in Power BI.

Read Microsoft Power BI Combo Chart

How to use measures in Power BI

After creating our own measure, now we will see how to use this measure on Power BI.

To using this measure, we will create a visual on Power BI Desktop. For example, here we will create a clustered column chart to use this measure.

How to use Measure in Power BI Desktop
How to use Measure in Power BI Desktop

This is how to use Measure in Power BI Desktop.

Some other example of measure and it’s syntax

SUM – This is used to add all numbers of a column.

Measure name = SUM(column name)

For example, previously we evaluate the Total sales(measure name) using this.

SUMX – It returns the sum of an expression evaluated for each row in a table.

Measure Name = SUMX(Table, Expression)

CALCULATE – It evaluates an expression in a context modified by a filter.

Measure name = (Expression, [filter1], [filter2]...)

COUNT – It counts the numbers in a column.

measure name = COUNT(column name)

AVERAGE – It returns the average value or an arthmatic mean all the numbers in a column.

measure name = AVERAGE(Column name)

FILTER – It returns a table that has been filtered.

Measure name = FILTER(Table,FilterExpression)

FIND – It returns the starting position of one text string within another text string. Most importantly, it is a case-sensitive.

masure name = FIND(FindText,WithinText,[StartPosition],[NotFoundValue])

SEARCH – It is similar to FIND. But It is not case-sensitive.

measure name = SEARCH(FindText,WithinText,[StartPosition],[NotFoundValue])

Apart from these, we can create different types of measures according to our requirements. We can create aggregate totals using Measure. In Power BI, almost all the measures are created using DAX expressions.

Read How to use Microsoft Power BI Scatter Chart

Using Mesause with a Slicer in Power BI

Now we will see how to use measure with a slicer on Power BI Report.

Step-1:

Select the blank space next to the chart. Then select a slicer visual under the visualizations. It will create a blank slicer visual on the report page.

Using Measure with a Slicer
Using Measure with a Slicer

Step-2:

Drag the Year field from Order date to the slicer visual.

Use Measure with a slicer
Use Measure with a slicer

Step-3:

Click on select the type of slicer. Convert the slicer between to List.

Use Measure with a slicer
Use Measure with a slicer

Step-4:

When we click on any value from the slicer’s list, it will recalculate the measure and visualize in a column chart like this:

Using Measure with a slicer on Power BI report
Using Measure with a slicer on Power BI report

This is how to Using Measure with a slicer on the Power BI report.

Read How to use Power bi maps

Use measure in another measure in Power BI

We can also use an existing measure in a new measure, that we will create. For example, we will create a measure that calculates the Net Sale. To implement this we have to follow these steps:

Step-1:

Go to Modelling/Home tab > Click on New measure.

Step-2:

Then enter this expression to create a new Measure for Net Sale.

Net sales = SUM(Orders[Profit]) + [Total Sales]

Here we use an existing measure i.e. Total sale, and another measure we created SUM of Profit. We use both measures in another measure.

Press Enter to calculate and save the measure in the Field panel.

Step-3:

Now we can use this new measure(Net sales) on our existing visualization or we can create a new visualization.

Use Measure in another Measure
Use Measure in another Measure

This is how to use Measure in another Measure.

Edit Measure Power BI

We can edit the existing Measure on Power BI by clicking on that measure or hover on the existing measure. For example, we have a measure naming as Up/Down Measure Calculate.

Edit Measure on Power BI
Edit Measure on Power BI

We can expand this formula bar by clicking that down arrow symbol. This is how we can edit an existing measure on Power BI.

You may like the following Power Bi tutorials:

Conclusion

From this above Power BI Tutorial, we have learned about Measures in Power BI. Also, we have discussed:

  • What is measure?
  • Disucuss about automatic measures available and how to use these on Power BI?
  • How to create measures in Power BI Desktop?
  • How to use measures in Power BI Desktop?
  • what are the other measures with syntaxes?
  • How to use Measure with a Slicer?
  • How to use Mesure in another measure?
  • How to edit an existing measure on Power BI?
>