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 is often called “calculated measures,” which use DAX expressions to calculate new values from the existing table.

You are free to use this image on you website, templates, etc., Please provide us with an attribution linkHow to Provide Attribution?Article Link to be HyperlinkedFor eg:Source: Measures in Power BI (wallstreetmojo.com)

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 with us.

How to Create & Use Measures in Power BI?

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

From this table, we do not have the “Total Sales” value to calculate the total sales value. However, we can insert the calculated column to calculate total sales.

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

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

  • 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.

  • After the above formula, press the “Enter” key. We will have a new column in the table.

  • Similarly, calculate two more columns for “Gross Profit” and Gross Profit %Gross Profit %Gross profit percentage is used by the management, investors, and financial analysts to know the economic health and profitability of the company after accounting for the cost of sales. Gross profit percentage formula = Gross profit / Total sales * 100%

  • read more.Below is for Gross ProfitGross ProfitGross Profit shows the earnings of the business entity from its core business activity i.e. the profit of the company that is arrived after deducting all the direct expenses like raw material cost, labor cost, etc. from the direct income generated from the sale of its goods and services.read more (GP).

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

  • 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.”

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

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

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

  • Close the bracket and press the “Enter” key to get the new measure in the table.

  • Similarly, get the Gross Profit (GP) overall summary value in the new Power BI measure.

  • Now, let us 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 and first open up the new measure.

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

  • In the next line, you can continue the formula to jump to the next line and press the “Enter” key by holding the “ALT” key together.

  • Open the CALCULATE function.

  • For the Expression supply, we need to get the SUM. So, we must open the SUM function in excelSUM Function In ExcelThe SUM function in excel adds the numerical values in a range of cells. Being categorized under the Math and Trigonometry function, it is entered by typing “=SUM” followed by the values to be summed. The values supplied to the function can be numbers, cell references or ranges.read more inside the CALCULATE function.

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

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

  • For this supply, select the “City” column.

  • Close two brackets and press the “Enter” key to get this new Power BI measure.Using these measures, we can calculate each city’s contribution percentage. Enter the new measure column and name it “Sales %.”

  • To calculate “Sales %,” enter the formula “Overall Sales / All-City Sales * 100”.

  • Press the “Enter” key to finish the formula.Using these new measures, we will build a table to show each city’s sales percentage. Insert “Table” visual from visuals.

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

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

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

Note: We can also download the Power BI dashboard file from the link below and view the final output.

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.

This article 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 and explanations. You can learn more about data visualization from the following articles: –

  • Creating Reports in Power BIPower Bi TutorialUsing Power Query in Excel