What are Measures in Power BI?
Measures in Power BI are calculations or aggregations you create to process your data, such as sum, average, count, or any custom calculation. Measures are often used in Power BI to derive insights from your data and create meaningful visualizations. They are written using Data Analysis Expressions (DAX), a formula language specific to Power BI and other Microsoft products like Excel.
Table of contents
Key Takeaways
- Measures in Power BI are calculations that provide insights into your data. To create a measure, use the “New Measure” button in the “Modeling” tab and write a DAX formula.
- Measures can be used in visuals to display aggregated or calculated data.
- DAX is the formula language used for creating measures, and it offers a wide range of functions for complex calculations. Keep your measures organized in folders for better management as your model grows.
How to Create Measures in Power BI?
To create new Power BI measures, follow the steps highlighted below:
Step 1: Open your Power BI Desktop application. Load or import your data source into Power BI.
Step 2: Once imported into Power BI, navigate to the Fields pane to view the data attributes of the dataset.
Step 3: Select the table you want to create a measure for. Right-click on the table and choose the New measure option.
Step 4: Write your DAX measures in Power BI in the formula bar at the top. Give it a descriptive name and Press Enter to create the measure.
Step 5: You can also use the New quick measure option to create new measures. Select the New quick measure option when you right-click on the table you want to create a measure for.
It will open a Quick measure window where you can choose the predefined measures for your visualization requirements.
Step 6: Select a calculation to create a measure for your visual from the Select a calculation dropdown.
Power BI quick measure supports multiple in-built measures ranging from Aggregate per category, Filters, Time intelligence functions, Totals, and Mathematical operations functions to be used across the visuals.
Once you choose a measure, Power BI generates its DAX expression and creates a measure for your table.
Step 7: Choose a calculation and specify the sections for the measures, such as Base value, Date, and Number of periods. These sections may vary depending on your chosen calculation from the Calculations dropdown.
It will generate a DAX expression for the chosen measure in Power BI.
Step 8: Navigate to the Fields pane to view the created measure for the table
Note: You can also create dynamic measures in Power BI as per user interaction through slicers using functions like SWITCH, IF-ELSE, etc.
Similarly, you can also create group measures in Power BI. For this, you can create display folders and drag and drop your measures into the folder.
In the next section, we will see how to use new Power BI measures.
Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)
If you want to learn Excel and VBA professionally, then Excel VBA All in One Courses Bundle (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.
How to Use Measures in Power BI?
Once you’ve created measures, you can use them in visuals and calculations.
To use new measures in Power BI, follow the steps highlighted below:
Step 1: Navigate to the Visualizations pane and select a visual icon (For example Pie chart, Donut chart, stacked column chart, etc.)
Step 2: Navigate to the Fields pane and drag and drop the field attributes, including the measures created on the dataset table, to field sections of the visual or the report canvas.
Step 3: Customize your visuals by adding dimensions, filters, or slicers as needed.
Step 4: Refresh your report to see updated results when the data changes.
Examples
This section will demonstrate how to create and use new measures in Power BI in a step-by-step process with two examples.
Example #1
Here, we will create and use a measure in Power BI showing the total valuation of the Top 100 companies in the world using the Top_1000_Companies_Dataset data. This dataset contains the details of the Top 1000 global companies along with their funding and valuation information.
To create a measure in Power BI, follow the steps highlighted below:
Step 1: Open the Power BI Desktop, import the Top_1000_Companies_Dataset dataset using the Get Data option, and click on the Load button.
Once the data is loaded, you shall be able to view it by navigating to the Fields pane.
Step 2:
- Select the Top_1000_Companies_Dataset table.
- Right-click on it.
- Select the New measure option from the context menu.
Step 3: Write your DAX measure in Power BI in the formula bar at the top. Give your measure a descriptive name and Press Enter or Click on the Commit icon to create the measure.
Here, we have created the Total Valuation of Top 100 companies measure by filtering the Top 100 companies and summing their valuations.
Total Valuation of Top 100 companies = SUMX(FILTER(Top_1000_Companies_Dataset, Top_1000_Companies_Dataset[GrowjoRanking] < 101), Top_1000_Companies_Dataset[valuation])
You can view the measure in the Fields pane.
Step 4:
- Navigate to the Visualizations pane.
- Select the Card visual icon.
- Drag and drop the measure created to the Fields section.
It will create a Card visual showing the total valuation of the Top 100 companies in the world.
Example #2
In this example, we will create a measure of Sales YoY% and use it in data visualization using the Country_Product_Sales dataset. Country_Product_Sales dataset contains the details of sales data across different geographies, products, and segments.
To create and use measures, follow the steps highlighted below:
Step 1: Open the Power BI Desktop, import the Country_Product_Sales dataset using the Get Data option, and click the Load button.
You can also use the Transform Data option to apply any data transformation or cleaning.
Once the data is loaded, you shall be able to view it by navigating to the Fields pane.
Step 2: Select the Country_Product_Sales table, right-click on the Country_Product_Sales table, and select the New quick measure option from the context menu.
It will open a Quick measure window with built-in measures you can choose from.
Step 3: Select a calculation to create a measure for your visual from the Select a calculation dropdown on the Quick measure window.
Here we have chosen Year-over-year change to assess the overall Sales change across all the products and segments.
Step 4: Specify the measure details such as Base value, Date, and Number of Periods sections by dragging and dropping the fields from the Fields pane. Click on the Add button once you have provided the data fields.
Note: These sections may vary depending on the measure you choose from Select a calculation dropdown.
Step 5: Once the changes are added, a new measure will be created for the Country_Product_Sales dataset.
Automatic DAX measures in Power BI are created based on your chosen measure. You can see the DAX expression by selecting the measure in the Fields pane.
[Sales YoY%] =
IF(
ISFILTERED(‘Country_Product_Sales'[Date]),
ERROR(“Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column.”),
VAR __PREV_YEAR =
CALCULATE(
SUM(‘Country_Product_Sales'[ Sales]),
DATEADD(‘Country_Product_Sales'[Date].[Date], -1, YEAR)
)
RETURN
DIVIDE(SUM(‘Country_Product_Sales'[ Sales]) – __PREV_YEAR, __PREV_YEAR)
)
Step 6:
- Navigate to the Visualizations pane.
- Choose the Matrix visual icon.
- Drag and drop the fields from the Fields pane to the respective sections, such as Rows, Columns, and Values, as shown below.
Step 7: Navigate to the Visualizations pane, choose the Card visual icon, and drag and drop the created measure from the Fields pane to the Fields section.
Here we have mapped the Sales Yoy% measure to the Fields section.
Step 8: Apply formatting to the visuals by navigating to the Format your visual tab under the Visualizations pane.
Post formatting, you can view the visual in the report canvas.
Important Things to Note
- When you interact with visuals in Power BI, measures are calculated on the fly, so they always reflect the most current data.
- DAX is a powerful formula language for creating measures and calculated columns. Take the time to learn DAX functions to create complex calculations.
- You can use measures to create Key Performance Indicators (KPIs), track trends, calculate ratios, and much more.
Frequently Asked Questions (FAQs)
To add two measures together in Power BI, you can create a new measure that sums or combines the two existing measures. Here’s how you can do it:
Step 1: Open your Power BI Desktop and load the dataset.
Step 2: Navigate to the Fields pane, right-click on the table, and click on New measure option to create measures.
Step 3: Enter the DAX expression in the formula bar to create measures and hit the Enter button. It will create measures on the selected table. You can follow these steps to create 2 measures.
Step 4: Navigate to the Fields pane, right-click on the table, and click on the New measure option.
Step 5: In the formula bar, enter the DAX expression to add those 2 measures you have created in the above steps
For example, to add Measure1 and Measure2, you can use the following formula:
NewMeasure = [Measure1] + [Measure2]
Step 6: Press Enter to create the new measure in Power BI
Organizing your measures into folders can help you manage your Power BI model more effectively. Here’s how you can create a folder for measures:
Step 1: Navigate to Model view from the left edge of the Power BI Desktop.
Step 2: Select the measure
for which you want to create a folder from the Fields pane.
Step 3: In the Properties pane, enter a name for the new folder in the Display folder to create a folder.
Step 4: Navigate to the Fields pane to view the folder for the measure.
To delete multiple measures in Power BI, follow the steps highlighted below:
Step 1: Navigate to the Fields pane and select the table containing the measures you want to delete.
Step 2: Expand the table fields and select the measure you want to delete
Step 3: Right-click on the selected measure and choose Delete from model option from the context menu.
Step 4: Confirm the deletion in the prompt that appears by choosing Yes button.
Post confirmation, the measure will be deleted from the table. You can repeat these steps for multiple measures in Power BI.
If you want to create a new measure that sums up existing measures, you can follow the steps mentioned in the first point for adding two measures. You can use simple addition to sum measures or you can use the SUMX function to sum multiple measures, depending on your specific requirements.
For example, if you have 2 measures Total Sales Amazon and Total Sales Walmart that you have created on the Sales_details table and you want to create a new measure that sums them, you can use the following DAX formula:
AddMeasure = SUMX(Sales_details,Sales_details[Total Sales Amazon] + Sales_details[Total Sales Walmart])
Recommended Articles
This has been a guide to Measures in Power BI. Here we learn how to create and use measures in power BI with examples and points to remember. You can learn more from the following articles –
Leave a Reply