Power bi measure examples (20 useful examples)

In this power bi tutorial, we will discuss the different types of Power Bi measure examples. The Power Bi measure examples are:

Power bi measure concatenate two columns
Power bi measure compare two columns
Power bi measure distinct count
Power bi measure for the sum of the previous month
power bi measure for max text
Power bi use date slicer value in the measure
Measure from 2 tables in power bi
Create a measure for average monthly sales power bi
Month 1 – 12 measure power bi
Power bi buid a measure based of total of
Power bi measure to filter on condition
Power bi measure remove nulls
Power bi year to date measure
Calculate average of Measure in Power BI
Power BI Conditional formatting text based on measure
Power BI filter Top10 by measure
RANKX Power BI Measure
Power BI View all Measure
How to clear filters in a measure power bi
YOY measure for revenue power bi

Power bi measure examples

Before discussing the 20+ Power BI measure examples, check out an article on what is a measure in Power Bi, this also covers, how to create a measure in Power BI, etc.

Also, you should know Power bi calculated column vs measure

Power bi measure concatenate two columns

Here we will see how to concatenate two columns using the measure in power bi desktop.

We will use the below sample table, this table contains employee id, first name, and last name.

Power bi measure concatenate two columns

Here we will concatenate the employee’s first name and last name by using concatenate() in measure.

Load the data using get data.
Then click on the New measure from the ribbon in power bi desktop.
Then write the below measure:

Full Name = CONCATENATE (
SELECTEDVALUE ( 'Emp'[First name]  )&" ",
SELECTEDVALUE ( 'Emp'[Last name] )
)

Now to check the measure, select the table visual from the visualization pane.
In the value field, drag and drop the employee id, first name, last name and Full name measure.

Read How to create a Measure based on Slicer in Power BI

Power bi measure compare two columns

Here we will see how to compare the amount of two years and then find the difference.

We will use the below sample table to compare the two years’ amount in power bi desktop.

Load the data using get data in power bi desktop.
We will create a measure which will find the difference between the amount of two year.
Click on the new measure from the ribbon.
Then write the below measure:

Diff = 
IF (
    HASONEVALUE ( 'Table'[Year] ),
    BLANK (),
    CALCULATE (
        SUM ( 'Table'[Amount] ),
        FILTER ( 'Table', 'Table'[Year] = MAX ( 'Table'[Year] ) )
    )
        - CALCULATE (
            SUM ( 'Table'[Amount] ),
            FILTER ( 'Table', 'Table'[Year] = MIN ( 'Table'[Year] ) )
        )
)

And now you can find the % of increase by using measures.

Click on the New measure from the ribbon
Then write the below measure:

%Increase = 
DIVIDE (
    [Diff],
    CALCULATE (
        SUM ( 'Table'[Amount] ),
        FILTER ( 'Table', 'Table'[Year] = MIN ( 'Table'[Year] ) )
    )
)

Now you can check the measure, select the matrix visual from the visualization pane.
In the rows field, drag and drop the region and year column from the field pane.
Then in the vallues field, drag and drop the Amount column, Difference measure and and %increase measure from the field pane.

Read Power bi sort by measure

Power bi measure distinct count

Here we will see how to use distinct count function in power bi measure with example.

We will use the below sample table to calculate the distinct count of the Enterprise column based on the Environment column.

Load the data using get data in power bi desktop.
Now to calculate the distinct count of enterprise column based on Enviroment column, for this we will create a measure.
Click on the new measure from the ribbon.
Then write the below measure:

DCMeasure = SUMX(VALUES('Table 3'[Enterprise]),DISTINCTCOUNT('Table 3'[Environmenet]))

Now to check the measure, select the table visual from the visualization pane.
In the value field, drag and drop the Enterprise column, environment column and DCMeasure measure from the field pane.

Read Power BI Measure IF with Examples

Power bi measure for the sum of the previous month

Here we will see how to calculate the sum of the amount of the previous month using power bi measure.

We will use the below sample data to calculate the sum of the amount of the previous month.

Power bi measure for the sum of the previous month

Load the data using get data.
Click on the new measure to calculate the sum of amount of previous month.
Then write the below measure:

Previous month = CALCULATE(SUM(Data[Amount]),FILTER(ALL(Data), 'Data'[Month number] = MAX('Data'[Month number]) - 1))

Create a table visual, add the date column, amount column and prevous month measure
Create a slicer with month number, and then select the previous month number.
In the below screenshot you can see the sum of amount of previous month.

Read Power bi measure subtract + 7 useful examples

Power bi measure for max text

Here we will see how to return the text of the maximum value using the measure in Power BI.

We will use the below sample data, to get the greatest contribution to the overall income as a value

Here we will create a measure that will return the maximum value in the table instead of the value it will return the type. So, here it will return Donations.

Load the data using get data.
Click on the new measure from the ribbon.
Then write the below measure:

Greatest Contributor = 
MAXX(
    FILTER(
        'Data1', 
        [Actual] = MAX('Data1'[Actual])
    ), 
    [Type]
)

Now to check the measure, create the card visual from the visualization pane.
In the fields, drag and drop the Greatest contributor measure from the field pane.

Read Power bi measure by category + Examples

Power bi use date slicer value in the measure

Here we will see how to calculate a value based on a date slicer using the measure in power bi desktop.

We have two tables one is project scheduler, and another one is Dim date( calculated table)

Project Scheduler

power bi use date slicer value in measure — power bi use date slicer value in the measure

Dim Date

I have created a calculated dim date table by using the below DAX formula in the calculated table.

dimDate1 = 
CALENDAR ("01/01/2018", "30/06/2018")

Now we will calculate the desired status of the project within the time period(1/1/2018-30/06/2018), for we will follow the below logic:

If start date falls within the selected date range(1/1/2018-30/06/2018) status (P2): opened
If both start date and end date falls within the selected date range status(P1):opened and closed.
If start date falls before the selected date range but end date falls within the date range status(P3): Closed.
If start date before and end date after the selected date range status (P9) is :Open
If start date after selected date range is(P5 and P7): NA

Now let’s see how to implement the above logic to get the desired result using measure:

Load the data using get data.
Then create the dim date table by using the above dax formula.
To get the desired result, click on the new measure from the ribbon.
Then write the below measure:

Measure1 = VAR RangeStart = CALCULATE(MIN(dimDate[Date]), ALLSELECTED(dimDate))
    VAR RangeEnd = CALCULATE(MAX(dimDate[Date]), ALLSELECTED(dimDate))
    VAR StartDate = MIN('Project Scheduler'[StartDate])
    VAR EndDate = MAX('Project Scheduler'[EndDate])
    RETURN SWITCH(TRUE(),
               StartDate >= RangeStart && StartDate <= RangeEnd &&
                   EndDate >= RangeStart && EndDate > RangeEnd, "Opened",
               StartDate >= RangeStart && StartDate <= RangeEnd &&
                   EndDate >= RangeStart && EndDate <= RangeEnd, "Opened , Closed",
               StartDate < RangeStart && StartDate <= RangeEnd &&
                   EndDate >= RangeStart && EndDate > RangeEnd, "Open",
               StartDate < RangeStart && StartDate <= RangeEnd &&
                   EndDate >= RangeStart && EndDate < RangeEnd, "Closed",
               "NA")

Create a slicer add the date column from the dim date i.e selected date range.
Create a table visual start date, end date, project name, status as on date, and measure 1, from the field pane.

Read Power BI Measure SUM

Measure from 2 tables in power bi

Here we will see how to calculate measures based on two tables in power bi desktop.

We have two tables one is the hiring table and another table is the employee table. From these tables, we will calculate the hire rate % by using measures.

Hire table

Employee table

Now we will calculate the hiring rate based on two tables which will count of id in the hiring table divided by the count of id in the employee table and then we will change the format of the measure to percentage.

Load the data using get data.
Click on the New measure to calculate the hire rate based on two tables.
Then write the below measure.

Measure = DISTINCTCOUNT('Hire'[Id])/(DISTINCTCOUNT('Hire'[Id])+ DISTINCTCOUNT('Employee address'[Id]))

Now to check the measure, create a card visual from the visualization pane.
In the fields, add the measure from the field pane

Read Countif function in Power BI Measure + 10 Useful Examples

Create a measure for average monthly sales power bi

Here we will see how to calculate the average of sales by divide it by the number of months using power bi measure.

We will use the below sample data to calculate the average of sales based on a number of months using power bi measure.

create a measure for average monthly sales power bi

In the above table, the total sales are 250, and there are 2 months January and February. So we will divide the total sales by 2 i.e 250/2= 125.

Load the data using get data.
First we will create a calculate column to display the month number.
Go to modelling -> New column from the ribbon.
Then write the below dax formula.

Month no. = MONTH(Table1[Date])

Now we will create a measure to calculate the average sales based on number of month.
Click on the new measure from the ribbon, then write the below measure

MeasureAverage = SUM(Table1[Sales])/DISTINCTCOUNT(Table1[Month no.])

To check the measure create the table visual, then add the date, sales, and measure average from the field pane.

Read Power bi measure switch statement with examples

month 1 – 12 measure power bi

Here we will see how to calculate total sales of the previous 12 months using the measure in power bi desktop.

We will use the below sample data, having month sales and year column.

month 1 - 12 measure power bi — month 1 – 12 measure power bi

From the above table, we will calculate the previous 12-month sales.

For example, if we click on 8/2018 then it should show rolling 12 months previous total sales.

For this, we will create a calendar table and two calculated columns in the original table. And then we will create a measure that will calculate the previous 12 months total sales

So, load the data using get data
We will create a yearmonth column, so, click on the modelling tab-> New column.
Then write the below Dax formula:

YearMonth = Data[Year]*100+Data[Month]

We will create a order column based on year month column, so, click on the modelling tab -> New column.
Then write the below dax formula:

Order = RANKX(Data,Data[YearMonth],,ASC )

Now we will create a calendar table, and then we will create a duplicate of the order column present in the original table.
So click on the Modelling tab -> New table, then write the below Dax formula

Calendar = DISTINCT(Data[YearMonth])

Create a calculated column in the calendar table, click on the new column from the ribbon.
Then write the below dax formula:

Order_Calendar = RANKX('Calendar','Calendar'[YearMonth],,ASC)

Now we will create a measure which will calculate the last 12 months’ total sales.

Click on the new measure from the ribbon.
Then write the below measure:

Rolling 12 months = IF(HASONEVALUE('Calendar'[YearMonth]),CALCULATE(SUM(Data[Sales]),FILTER(ALL('Data'[Order]),'Data'[Order]>=SELECTEDVALUE('Calendar'[Order_Calendar])-11&&Data[Order]<=SELECTEDVALUE('Calendar'[Order_Calendar]))),CALCULATE(SUM(Data[Sales])))

Now to check the measure, create the table visual from the visualization pane.
In the value field, drag and drop the yearmonth column and measure from the field pane.
Now create a slicer to filter the table visual based on the year month.
In the fields, add the Yearmonth column from the calendar table.
Now click on the date in the slicer, it will filter the table visual and it will show the total sales for last 12 month.

Read Power BI Measure Filter

Power bi buid a measure based of total of

Here we will see how to calculate the total quantity based on status using power bi measure.

We will use the below sample table having two columns that are status, and qty.

power bi buid a measure based of total of

Here we will calculate the total qty for the status p using measure, then we will show it in the card visual.

Load the data using get data.
Click on the new measure from the ribbon.
Then write the below measure:

Measure 2 = CALCULATE(SUM(Table2[Qty]), Table2[Status]="P")

Now to check the measure create a card visual, from the visualization pane.
Then in the field, drag and drop the measure from the field pane.

Read Power BI Measure Date + Examples

Power bi measure to filter on condition

Here we will see how to filter a column based on condition using the measure in power bi desktop.

We will use the below sample data which have two columns student and qualification.

Here we will filter the students that have qualifications in mathematics and Physics.

For this, we will create a measure, that decides whether the student has chosen both of the qualifications. If the measure returns 1 then the student chosen both the qualification otherwise return 0.

Then we will put that measure to the visual level filter in the power bi desktop.

Load the data using get data.
Click on the new measure from the ribbon.
Then write the below measure:

Both the qualification = IF( CALCULATE( DISTINCTCOUNT(Table1[Qualification]),ALLSELECTED(Table1))=CALCULATE(DISTINCTCOUNT(Table1[Qualification]),ALLSELECTED(Table1[Qualification])),1,0)

Now create a slicer with the qualification column and then create a table visual with student and qualification.
Now click on the table visual, then in the filter pane, add the measure to the field.
Then set show item with no value is 1, click on Apply filter.
In the slicer, select the qualification mathematics and physics.

Read Power bi measure divide + 8 examples

mom% measure for revenue power bi

Here we will see how to calculate the month-over-month percentage for revenue using power bi measure.

We have two tables one table is the fact table which contains the date column, month column, and amount column.

And another table is the DimDate table which is a calculated table created by using the below function:

DIMTIME = CALENDARAUTO()

To calculate the mom%

First step, we need to arrange the data in such a way that we can visualize the amount for current month and previous month
Then we need to calculate the difference between the this month value and previous month value.
Now the last step is to calculate the percentage of month over month, for this we need to divide the difference by the previous month value.

Mom% formula = (This month- Previous month)*100/ Previous month.

Load the data using get data.
Then click on the new measure from the ribbon.
Then write the below measure:

Mom% = VAR PRE = CALCULATE(SUM('Fact table'[Amount]),DATEADD('Fact table'[Date],-1,MONTH))
VAR CUR = CALCULATE(SUM('Fact table'[Amount]))
RETURN
IF(ISBLANK(PRE),0,(CUR-PRE)/PRE)

Click on the measure, then change the format to percentage from the measure tool.
Now to check the measure, click on the table visual from the visualization pane.
In the value field, drag and drop the name, month, Amount columns, and mom% measure from the field pane.

Read Power BI Measure multiply with examples

Power bi measure remove nulls

Here we will see how to hide or remove null values from the power bi matrix visual using power bi measure.

We will use the below sample data, have name column, type column, and value column.

Now if we will create a matrix simply plotting data, you can see there is null or blank in between the value.

Now our desired result can see in the below screenshot when we select S1 and S3 in the slicer.

So, to get the desired result, we will create a measure.
Click on the new measure from the ribbon.
Then write the below measure.

IgnoreNullValues = IF (CALCULATE(DISTINCTCOUNT('Sheet'[Type]), ALLSELECTED('Sheet'[Type])) 
     = COUNTROWS(ALLSELECTED('Sheet'[Type])), 
 MIN('Sheet'[Value]) 
 )

Now in the matrix, instead of values in the value field add IgnoreNullMeasure from the field pane.
Create the slicer add the type column from the field pane.
Select S1 and S3 using cntrl +select, now you can see the desired result.

Read Power bi measure count with filter

Power bi year to date measure

Here we will discuss Power Bi year to date (YTD) measures using TotalYTD() in power bi desktop.

The TotalYTD () is the time intelligence function, this function will total up an expression using a specified date field that you can apply a filter to and specify the year ending date.

The syntax of TotalYTD is

TotalYTD = (Expression,
    [dates],
    filters,
    year ending date,
)

We will use the Bi data, to calculate the year-to-date income of the person whose education level is PG_Masters.

Load the data using get data.
Then click on the new measure to calculate the year to date income.
Then write the below measure.

YTD = TOTALYTD(
    SUM( BIData[Income]),
    BIData[Date],
    BIData[Education_level]="PG_Masters"
)

To check the measure click on the table visual, Then add the date column Education level column, Incone column and YTD measure from the field pane.
The in the filter pane, expand the Education level column, check the PG_Master.

Read How to get selected value from Slicer in Power BI

Calculate average of measure in power bi

Here, we will see how to create a measure that will calculate the average of another measure.

Let’s take an example of a simple data table, that we have created based on the product’s ID, its sale’s date, and amount.

calculate average of measure in power bi — calculate an average of measure in Power BI

Now we will create a measure that calculates the total sales of the Product.

Total sale = SUM('Table'[Sale])

Again, we will create another measure to calculate the average of another measure i.e. Total sales. For this:

Average sales Per day = AVERAGEX(VALUES('Table'[Date]),[Total sale])

Here we have created a Stacked column chart to visualize the average. This is how to calculate an average of measure in Power BI.

Read Power BI Date Function with Examples

Conditional formatting text based on measure

In Power BI we will see how to do formatting a text field using condition on Measure. Here is a step by step guide to execute this:

Step-1:

Let’s take an example of a simple data table having Projects and their related departments with empty fields.

Conditional formatting text based on measure — Conditional formatting text based on a measure

Step-2:

Now will create a measure that will be identified whether the field is empty or not.

Test = 
     VAR Dept = SELECTEDVALUE('Table'[Department]) 
     RETURN IF(Dept <> BLANK(), Dept, "No Dept")

Drag this measure to the table:

Step-3:

Now we will create another measure using the logic of the Test measure. Then apply conditional formatting on the text field using this measure.

Colour Project =
    VAR Dept = SELECTEDVALUE(Projects[Department])
    RETURN IF(Dept <> BLANK(), "#40E0D0", "#FFA07A")

Step-4:

To apply conditional formatting, clicking on the down arrow of the Project > Conditional formatting > Background color.

Conditional Formatting the text based on measure — Conditional Formatting the text based on the measure

Step-5:

It will redirect to a page where we will apply the conditional formatting on the text field using the measure that we created previously i.e. Colour Project. Then click On OK.

Step-6:

Now we can see the project column got formatted according to conditions like this:

Conditional fomatting on Text field using Measure — Conditional formatting on Text field using Measure

Step-7:

Like Text background, also we can do formatting on the font of the text field. For this, we will create a new measure that will format the color whether the field is empty or not.

Colour Test = 
    VAR Dept = SELECTEDVALUE('Table'[Department])
    RETURN IF(Dept <> BLANK(), "Green", "Red")

Drag this measure to the table, this will return as text like this:

Conditional formatting on Text field using Measure

Step-8:

To format the font of the text, click on the down arrow of the Colour Text > Conditional formatting > Font color.

Then put the condition to formatting the font of the text:

Now we can see our text field is fully formatted after applying conditional formatting on it:

Conditional formatting on Text field using a Measure

This is how to do Conditional formatting on the Text field using a Power BI Measure.

Read Power bi show value as percentage + 13 Examples

Power BI Filter TOP 10 by measure

Now we will see how to filter the top 10 values from a data table using Measure. For this here we have created a Transaction table having users’ name and their amounts.

First, we will create a measure to calculate the total amount:

Total amount = SUM('Transaction'[Amount])

Again we create another Measure to calculate the ranking according to amount transaction:

Amount Ranking = RANKX(ALL('Transaction'),[Total amount],,DESC,Skip)

Finally, using these Measures, we will create another measure that will calculate the top 10 transactions:

Top10Transaction = CALCULATE([Total amount],FILTER('Transaction',[Amount Ranking]<=10))

Now we will use this Measure(Top10Transaction) on our transaction table to get the Top 10 amounts:

This is how we can filter the Top 10 by using Measure.

Read Power bi show items with no data

RANKX Measure Power BI

In Power BI, RANKX is a scalar function that returns the ranking of a specific number in a list of numbers in each row of a table.

We can use the RANKX function in both the calculated column and a Measure in Power BI. The syntax for this functions is:

Measure = RANKX(Table, Expression, [Value], [Order], [Ties])

For example, here we are going to use the Transaction table, that we have created previously.

Now we will create a Measure that will calculate the Ranking of the User according to their transaction like this:

User Ranking = RANKX ( ALL ( 'Transaction' ), [Total amount] )

This is how to use RANKX Measure in Power BI to calculate the Ranking.

Read Power bi sum group by multiple columns

Power BI View all Measure

In Power BI, we can see all the measures those we have used in our report or data model, in one place by following these steps:

Step-1:

For this, first, we have to download and install the DAX Studio on our local system. You can download the latest version from here.

After completing the Download, we can install it by just pressing through Next.

Step-2:

We can open the DAX studio from External tools on Power BI. Then click on Connect(By default, it showing that Power BI file, which is already open).

As our current PBI file is on Power BI measure examples, so by default is showing that file. Click on Connect.

Step-3:

Then insert the below expression on DAX studio and then press Run.

SELECT [NAME], [EXPRESSION] from $SYSTEM.TMSCHEMA_MEASURES

This is how we can view all the measures on Power BI through DAX Studio.

Total Sales All Products ALL() = CALCULATE([Total Sales], ALL(Products))

Read Power bi Date Difference – 8 Different Examples

How to clear filters in a Measure Power BI

Microsoft recently introduced a new function in DAX as REMOVEFILTERS() which is used to clear filters from the specified table or column. Its purpose is to act as a table filter parameter inside CALCULATE(). The syntax is:

REMOVEFILTERS(<Table/Column Name>, <ColumnName1>,...)

For example, here we have created a table having Product category and sales.

First, we will create a measure to calculate the total sales of the Product’s category.

Total sales = SUM(Orders[Sales])

Now we will create a measure using REMOVEFILTERS() to clear the filter from the table:

Total Sales All Products REMOVEFILTERS() = CALCULATE([Total sales], REMOVEFILTERS(Orders))

How to clear filters in a Measure Power BI

There is another filter function i.e. ALL() which works as same as REMOVEFILTERS(). For this the syntax is:

ALL(<Table/Column Name>, <ColumnName1>,...)

Total Sales All Products ALL() = CALCULATE([Total sales], ALL(Orders[Product Sub-Category]))

Now we will create a measure using ALL() to clear the filter from the table

This is how we can clear the filter using both functions as DAX measures in Power BI.

Read Power bi group by date range

YOY Measure for revenue Power BI

YOY stands for Year Over Year which returns a measure of growth. It calculates the growth between the total sum and the total sum of the previous year.

From Various Power BI DAX times intelligence functions, here we will introduce the SAMEPERIODLASTYEAR(). It returns a set of dates in the current selection from the previous selection.

Here is a step-by-step guide to calculating the YOY for revenue in Power BI.

Step-1:

Here we have created a sample data having orders for the last 2 years like below:

Again we have created a simple data table having some random dates.

Step-2:

First, we will create a measure that will calculate the total sales.

Total Sales = SUM('Orders'[Amount])

Step-3:

Now we will create 2 relationship between two tables. One is between order date(from Orders table) and Date(from Dates table). Another is between Ship date(from Orders table) and Date(from Dates table). For this go to model view page > Click on the more option of the Orders table > Manage relationship.

Then it will redirect to the page where we can create those relationships between two tables:

Similarly we can do a relationship in between Ship date and Dates. Then it will look like this:

create relationship between two tablecreate relationship between two table — create relationship between two table

We can see the relationships are created in between two tables:

Step-4:

By using SAMEPERIODLASTYEAR() function we will create a measure like this:

Total Sales Last Year = CALCULATE([Total Sales], SAMEPERIODLASTYEAR(Dates[Date]))

It will comparing the each day’s current year and previous year. For example, 1st january’s current year sales 110 and last year sales 300.

Also, we can filter this data so that it will only show the data befor today.

Total Sales = CALCULATE(SUM(Orders[Amount]), FILTER(Dates, Dates[Date] < TODAY()))

and,

Total Sales Last Year = CALCULATE(CALCULATE([Total Sales], SAMEPERIODLASTYEAR(Dates[Date])), FILTER(Dates, Dates[Date] < TODAY()))

Now we will dreag these measure to the table:

Step-5:

Again we will create a measure that will count the order number and comparing it:

Count Sales = CALCULATE(COUNT(Orders[Order Number]), FILTER(Dates, Dates[Date] < TODAY()))

and,

Count Sales Last Year = CALCULATE(CALCULATE([Count Sales], SAMEPERIODLASTYEAR(Dates[Date])), FILTER(Dates, Dates[Date] < TODAY()))

Drag these measure to the table to see the differences:

This is how we can calculate the Year Over Year measure for revenue in Power BI. Similarly, we can do this by using Total sales in the Ship date. For this we will create the below measures:

Total Sales by Ship Date = CALCULATE(CALCULATE (
SUM ( Orders[Amount] ),
USERELATIONSHIP (
Orders[Ship Date],
 Dates[Date]
)
)
, FILTER(Dates, Dates[Date] < TODAY()))

and,

Total Sales by Ship Date Last Year = CALCULATE(CALCULATE([Total Sales by Ship Date], SAMEPERIODLASTYEAR(Dates[Date])), FILTER(Dates, Dates[Date] < TODAY()))

This is how to do Power BI YOY measure for revenue.