Measures in Power BI

Measures in power bi are a summarization of any data, it is important to have a summary of any data or be it any representation of data, in power bi we have tools to create our own measures based on the data itself, we also have the options to name the measures to the way we want.

What are Measures in Power BI?

Power BI Measures are the way of defining calculations in a DAX model, which helps us to calculate values based on each row, but rather, it gives us aggregate values from multiple rows from a table. Creating Power BI Measures are often called “Calculated Measures,” which uses DAX expressions to calculate new values from the existing table itself.

measures in power bi

Now we will see how we can create new measures in Power BI to play with the existing data set. You can download the workbook used in this example to practice along with us.

You can download this Measures in Power BI Excel Template here – Measures in Power BI Excel Template

How to Create & Use Measures in Power BI?

I have already uploaded the data to Power BI and my table different columns, as shown below.

measures in power bi (Data)

From this table, we don’t have the “Total Sales” value to calculate total sales value; we can insert the calculated column to calculate Total Sale.

  • To add a new column, right-click on the table and choose “New Column” under the fields section, as shown below.

New Column

  • This will open up a new column section in the “Formula Tab.”

measures in power bi (New Column - formula tab).png

  • First, we need to give the column a name before we apply the DAX expression formula. Give the name as “Total Sales” and enter the formula as shown below.

measures in power bi (total sales)

  • After the above formula, hit the enter key, and we will have a new column in the table.

total sales

measures in power bi (gross profit)

  • Below is for Gross Profit % (GP %).

measures in power bi (gross profit%)

  • Now using these new columns, we can create New Measures to get the aggregate total values. Right-click on the table and choose “New Measure.”

New Measure

  • This will also create a new column similar to new the above one, but this we could see the word “Measure.”

measures in power bi (measure - formula tab)

  • Now, if you want to get the overall sales amount, we can create that power bi measure by using DAX expressions. First, give a name to the measure as “Overall Sales” and open the SUM DX function.

measures in power bi (Sum)

  • As you can see above, the IntelliSense list started to show all the columns from the table. So choose “TV_Sales[Total Sales.”

measures in power bi (sum total sales)

  • Close the bracket and hit the enter key to get the new measure in the table.

Overall Sales

  • Similarly, get the gross profit (GP) overall summary value in the new power bi measure.

measure power bi (Overall Sales GP)

  • Now let’s create one more new measure in power bi to get the overall sales value against all the cities. For this, we need to use DAX functions, first open up the new measure.

measure power bi (Overall City sales)

  • Before you start the DAX formula, you can also insert a comment for this function by starting with two forward slashes.

measure power bi (Overall City sales formula)

  • In the next line, you can continue the formula to jump to the next line hit the enter key by holding the ALT key together.

measure power bi (Overall City sales formula new line)

  • Open the CALCULATE function.

measure power bi (Overall City sales Calculate)

  • For the Expression supply, which column we need to get the SUM since we need the SUM open SUM function in excel inside the CALCULATE function.

measure power bi (Overall City sales Calculate Sum)

  • For the SUM function, choose the “Total Sales” column.

measure power bi (Overall City sales Calculate Sum Total Sales)

  • The second argument is Filter 1 for this open ALL function.

measure power bi (Overall City sales Calculate Sum Total Sales, ALL)

  • For this supply, the “City” column.

measure power bi (Overall City sales Calculate Sum Total Sales, ALL, city)

  • Close two brackets and hit enter to get this new power bi measure.
  • Now using these calculated measures, we can calculate each city contribution percentage. Enter the new measure column and name it as “Sales %.”

measure power bi (Sales %)

  • Now to calculate Sales %, enter the formulas as “Overall Sales / All-City Sales * 100”.

measure power bi (Sales %, overall sales)

  • Hit the enter key to finish the formula.
  • Now using these new measures, we will build a table to show each city sales percentage. Insert “Table” visual from visuals.

Table Card

  • Now drag and drop “City, Overall Sales, Overall City Sales, and Sales %” to the “Values” field of “Table” visual.

Drag & Drop in VAlues

This should have created a visual of City-wise sales with their overall city-wise contribution.

Visual of City Wise Sales

Now we could see Ahmedabad city has contributed 17.69 % to the overall sales value. So like this using Measures in Power BI, we can get the new values by using DAX formulas.

Note: Power BI dashboard file can also be downloaded from the link below, and the final output can be viewed.

You can download this Power Bi Measures Template here – Power Bi Measures Template

Things to Remember Here

  • Calculated Columns and Calculated Measures are different from each other.
  • Using Measures, we can create aggregate totals.
  • Almost all the measures in Power BI are created with the help of DAX formulas.
  • Usually, all the measures are stored in a separate table for a better understanding of the measures.

Recommended Articles

This is a Guide to Measures in Power BI. Here we will learn how to create and use new measures to play with the existing data set in Power BI along with examples & explanations. You can learn more about data visualization from the following articles –

  • 6 Online Courses
  • 29+ Hours
  • Verifiable Certificate of Completion
  • Lifetime Access
LEARN MORE >>