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.
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.
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.
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.
- This 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, hit the enter key, and 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% .
- 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. (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.”
- This will also create a new column similar to new the above one, but this we could see the word “Measure.”
- 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.
- 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 hit 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’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.
- Before you start the DAX formula, you can also 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 hit the enter key by holding the ALT key together.
- Open the CALCULATE function.
- For the Expression supply, which column we need to get the SUM since we need the SUM open 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. 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, the “City” column.
- 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 %.”
- Now to calculate Sales %, enter the formulas as “Overall Sales / All-City Sales * 100”.
- 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.
- Now drag and drop “City, Overall Sales, Overall City Sales, and Sales %” to the “Values” field of “Table” visual.
This should have created a visual of City-wise sales with their overall city-wise contribution.
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.
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 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 –