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.

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.

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.

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:

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.

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

Step-2:

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

Step-3:

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

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.

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.

Step-2:

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

Step-3:

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

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:

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.

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.

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:

• Power bi show items with no data
• Power bi show value as percentage
• How to create a Power BI Dashboard in Microsoft teams
• Power bi sum group by multiple columns
• Power BI Matrix

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?