What is SUMX in Power BI?
In Power BI, SUMX is a DAX (Data Analysis Expressions) function that calculates the sum of a numerical column for each row in a table and then returns the total sum of those individual row sums. It’s a versatile function commonly used for creating more complex calculations and aggregations that involve iterating through rows in a table.
Table of contents
Key Takeaways
- SUMX is a DAX function in Power BI that iterates through rows in a table and sums up the results of a specified expression.
- It’s useful for more complex calculations that involve row-level interactions.
- Use it in calculated columns or measures in the “Model” view of Power BI.
- You can use nested SUMX in Power BI or SUMX with a filter option or calculate SUMX in Power BI.
- Be cautious with performance when using SUMX on large datasets.
- Remember that DAX functions in Power BI can be quite powerful but may also require a good understanding of your data model and the specific requirements of your analysis.
What does SUMX Function do in Power BI?
The SUMX function in Power BI iterates through each row of a table, evaluates a specified expression for that row, and then sums up the results of those expressions across all rows. It’s particularly useful when you need to perform calculations that can’t be easily achieved using standard aggregation functions.
The syntax of the SUMX function is as follows:
SUMX(Table, Expression)
- Table – refers to the table that contains the records or rows for which we will evaluate the expression
- Expression – refers to the expression to be evaluated for each record/row of the table.
How to use SUMX Function in Power BI?
To use the SUMX function in Power BI, follow these steps:
Step 1: Import and Load your data into Power BI using a data connection.
Step 2: Navigate to the Modeling tab and click on the New Measure option in the top menu to create a measure for the dataset.
Step 3: Write the SUMX function in the formula bar.
Total GDP of Developed Countries = SUMX(FILTER(‘World Data Set’,’World Data Set'[Status] = “Developed”),’World Data Set'[GDP])
Step 4: Click on the Commit icon (√) to create the measure and use it in your visualization.
You can create new columns to calculate SUMX in Power BI. You can also use nested SUMX in Power BI to perform complex calculations.
In the subsequent section, we will go through some of the examples to understand how you can use the SUMX function in Power BI to create measures or columns for your Power BI reports.
Examples of SUMX Function in Power BI
Here, we will see 3 examples which provide a step-by-step process to calculate SUMX. We will also show how to use SUMX in Power BI reports.
SUMX in Power BI – Example #1
In this example, we will create a Power BI report showing Total Revenue and Cost of Goods by Product and Year with a slicer option using the Financials dataset in Power BI. To use the Power BI SUMX function, follow these steps:
Step 1: Open the Power BI Desktop and import the Financials dataset using Data Connection. To load the dataset to Power BI, click on the Load button.
Once imported into Power BI, you will be able to view the data fields in the Fields pane.
Step 2: Navigate to the Fields pane, right-click on the Financials table, and choose the New measure option.
Then, you will see a prompt in the formula bar.
Step 3: Enter the DAX expressions in the formula bar to create the Power BI columns for the Financials table.
We have created a Total Revenue measure that calculates using the SUMX in Power BI DAX.
Total Revenue = SUMX(financials,financials[Units Sold]*financials[Sale Price])
Step 4: Click on the Commit icon to save the changes for each of the above changes to create the new measure.
Step 5: Navigate to the Visualization pane, choose the Stacked Column Chart visual, and drag and drop the attributes from the Fields pane to the report canvas to create a column chart visual.
Step 6: From the Visualization pane, add slicers to the Power BI report and apply the formatting options to make it visually appealing.
SUMX in Power BI – Example #2
In this example, we will create a Power BI report showing Total Revenue and Cost of Goods by Product and Year with a slicer option using the Financials dataset in Power BI. To use the Power BI SUMX function, follow these steps:
Step 1: Open the Power BI Desktop and import the World Data Set file using Data Connection. To load the dataset to Power BI, click on the Load button.
Once imported to Power BI successfully, you can see the data attributes in the Fields pane.
Step 2: Navigate to the Fields pane, right-click on the World Data Set table, and choose the New measure option.
Once you choose the New measure option, you will see a prompt in the formula bar.
Step 3: Enter the DAX expressions in the formula bar to create the Power BI columns for the World Data Set table.
We have created 2 different measures in the World Data Set table.
Total GDP of Developed Countries measure calculates the total GDP of the Developed countries across all years.
Total GDP of Developed Countries = SUMX(FILTER(‘World Data Set’,’World Data Set'[Status] = “Developed”),’World Data Set'[GDP])
Total GDP of Developing Countries measure calculates the total GDP of the Developing countries across all years.
Total GDP of Developing Countries = SUMX(FILTER(‘World Data Set’,’World Data Set'[Status] = “Developing”),’World Data Set'[GDP])
Step 4: Click on the Commit icon to save the changes for each of the above changes to create the new measures.
Step 5: Navigate to the Visualization pane, choose the Clustered column chart visual, and drag and drop the attributes from the Fields pane to the report canvas to create a Clustered column chart visual.
Step 6: Navigate to the Format your visual tab under the Visualization pane and apply formatting to the Power BI visual.
Once the formatting is completed, you can use the report for analysis or share it with other users in your organization.
SUMX in Power BI – Example #3
In this example, we will create a Power BI report showing Total Area and Land Area for the Top 10 countries by Ranks using the countries-table dataset in Power BI. To use the Power BI SUMX function, follow these steps:
Step 1: Open the Power BI Desktop and import the countries-table dataset using Data Connection. To load the dataset to Power BI, click on the Load button.
Once imported into Power BI, you will be able to view the data fields in the Fields pane.
Step 2: Navigate to the Fields pane, right-click on the countries-table table, and choose the New measure option.
Once you choose the New measure option, you will see a prompt in the formula bar.
Step 3: Enter the DAX expressions in the formula bar to create the Power BI columns for the Financials table.
We have created Total Area for top 10 countries and Total Land Area for top 10 countries measures for the countries-table.
Total Area for top 10 countries calculates the total area for the top 10 countries by rank using the SUMX in Power BI DAX.
Total Area of Top 10 countries = SUMX(FILTER(‘countries-table’, ‘countries-table'[rank] < 11),’countries-table'[area])
Total Land Area for top 10 countries calculates the total land area for the top 10 countries by rank using the SUMX in Power BI DAX.
Total Land Area for top 10 countries = SUMX(FILTER(‘countries-table’, ‘countries-table'[rank] <11),’countries-table'[landAreaKm])
Step 4: Click on the Commit icon to save the changes for each of the above changes to create the new measure.
Step 5: Navigate to the Visualization pane, choose the Card visual, and drag and drop the attributes from the Fields pane to the report canvas to create a card visual.
Here we have created 2 card visuals i.e. Total Land Area for Top 10 countries and Total Area for Top 10 countries.
Step 6: Navigate to the Format your visual tab under the Visualization pane to apply the formatting options to make it visually appealing.
Also, apply other formatting options by navigating to the General tab under Format your visual section in Visualization.
Post formatting, the Power BI report can be shared with users or can be used in dashboards.
Important Things to Note
- SUMX can be resource-intensive, especially with large datasets, so use it judiciously.
- It only considers numbers in a column. Texts, blanks, and logical values are not considered.
- The SUMX function is not supported in DirectQuery mode if you are using it in calculated columns or row-level security rules.
- Ensure that the columns you’re using in the expression are compatible and make sense in the context of the calculation.
Frequently Asked Questions (FAQs)
There could be several reasons why the SUMX function might not be working as expected:
• Incorrect Syntax: Check if you’ve written the SUMX function with the correct syntax. Make sure you have specified the table and the expression to be evaluated for each row.
• Column Compatibility: Ensure that the columns you’re using in your expression are compatible and make sense for the calculation.
• Filter Context: Understand the filter context in your report. The SUMX function is sensitive to the filter context and might not provide accurate results if the filters are not applied correctly.
• Data Model Issues: If your data model is not set up correctly, it might lead to unexpected results. Make sure your relationships and calculations are properly defined.
You can use the SUMX function in combination with the FILTER function to perform more advanced calculations.
Here’s a basic example of how to use SUMX and FILTER together.
In this example, we have created a measure to calculate the total area of the Top 10 countries by rank.
The SUMX in Power BI DAX is highlighted below:
Total Area of Top 10 countries = SUMX(FILTER(‘countries-table’, ‘countries-table'[rank] < 11),’countries-table'[area])
The filter function here filters out only top 10 countries based on rank field and then SUMX performs calculations on the filtered dataset to calculate the total area.
Recommended Articles
This has been a guide to SUMX Power BI. Here we look how to use SUMX function to create measures or columns in power bi report with examples. You can learn more from the following articles –
Leave a Reply