WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Blog
  • Free Video Tutorials
  • Courses
  • All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Learn Power BI » SUMX in Power BI

SUMX in Power BI

By Jeevan A YJeevan A Y | Reviewed By Dheeraj VaidyaDheeraj Vaidya, CFA, FRM

Sumx is a function in power bi which is also an inbuilt function and it is categorized under the mathematical functions, the use of this function is to return the sum of expression from a table and the syntax used for this function is as follows SUMX(<table>,<Expression>).

What Does SUMX Function Do in Power BI?

SUMX is an iteration function in Power BI that works on a row by row calculation as per the expression or equation is given. This function takes into consideration each row at a time and applies the calculation. This will not concentrate on the entire column, unlike the SUM function do, but it works like a cell be cell formula in excel. SUM is an aggregate function, and SUMX is an expression function. Power BI data manipulation can be done by using “DAX” functions, and SUMX is one such function in Power BI. In this article, we will take you through the SUMX in Power BI.

Below is the syntax of the SUMX function in Power BI.

SUMX Formula

Table: First thing we need to do is to supply the table name for which we are supplying the SUMX function.

Expression: After the table, we need to provide the Expression or Equation to do row by row.

To supplying Power BI SUMX, you need data to work with, so you can download the excel workbook template from the below link, which is used for this example.

Popular Course in this category
Sale
Power BI Training Certification
4.5 (521 ratings)
6 Online Courses | 29+ Hours | Verifiable Certificate of Completion | Lifetime Access
View Course

Power BI in SUMX

You can download this Power BI SUMX Excel Template here – Power BI SUMX Excel Template

Examples of SUMX Function in Power BI

Below are some examples of SUMX function in Power BI.

SUMX in Power BI – Example #1

For example, look at the below simple table.

SUMX Power Bi Example 1

  • In the above table, we have units and price per unit, but we don’t have Total Sale value. So by using power BI SUMX, we will find out what is the sale value.
  • Upload the data table to Power BI and name the table as “Sales Table.”

SUMX Power Bi Example 1-1

  • Now we need to calculate the “Total Sales” column as a new calculated column. Right-click on the table name and choose “New Column.”

SUMX Power Bi Example 1-2

  • Name the new column as “Total Sales.”

SUMX Power Bi (add column)

  • Open the SUMX function now.

SUMX Power Bi Example 1-4

  • First, we need to supply the table name so since our table name is “Sales Table,” supply the same only.

SUMX Power Bi Example 1-5

  • The expression is nothing that is the equation we need to do??
  • So we need to find the “Total Sales” value by multiplying Units with Price.

SUMX Power Bi Example 1-6

  • Close the bracket and hit the enter key to get the result in the new column.

SUMX Power Bi Example 1-7

Wow!!! It says total sales as 56658 for all the rows. This is because since we have used SUMX to arrive at the new column, it has given us the overall total amount for all the rows. So to arrive each row calculation, we need to apply the Power BI SUMX function in “New Measure” not in “New Column.”

  • Right-click on the table and choose “New Measure.”

SUMX Power Bi Example 1-8

  • Give measure a name as “Sale Value.”

SUMX Power Bi Example 1-9

  • Now apply the SUMX function in power BI.

SUMX Power Bi Example 1-10

  • Now come back to “Report Tab.”

Report Tab

  • Insert “Table” visual from the visualization list.

Add table

  • Drag and drop City and “Sale Value” to get the summary table.

Drag and drop

  • This is giving us the exact result but drag and drop the first calculated column, i.e., “Total Sales” to see the city-wise result.

Add total sales

This is absolutely giving us blunder results because it is adding overall value for all the cities in each row, so this is the wrong way of applying the SUMX function in Power BI.

SUMX in Power BI – Example #2

For the same table, we will do additional calculations. For example, assume for every transaction we are going to deduct 5% as the handling charges.

  • Right-click on the table and choose “New Measure” and give the name as “Sales after Charges.”
  • Enter the below formula now.

Example 2

  • Click on the Enter key. We will have the new measure.
  • Drag and drop the measure to the table to see the difference.

Example 2-1

As you can see, now we have Sale Value before deducting charges and “Sale after Charges” (SAC). For example, for Mumbai, “Sale Vale” was 17016 after deducting 5% charges. It is 16165. i.e. Mumbai = 17016 – (17016 * 5/100) = 16165.

SUMX in Power BI – Example #3

Now we will see nested calculations. For example, assume wherever the city name is “Bangalore,” we are going to give an additional 500 rupees discount. If not, the Bangalore discount will be zero.

  • So now we need to find out what is the “Sale After Discount” (SDA).
  • Apply the below measure to find the SAD.

Find the SAD

Let me explain to you the formula in detail.

  • In the “Sales Table,” If the City is “Bangalore,” then we need to deduct 500 from Sales After Charges (SAC), or else we need the result as same as Sale After Charge (SAC) only.
  • Now drag and drop the new measure to the existing table to see the difference.

Add SAD

  • As you can see above, only for “Bangalore” city sales amount has been changed, and for other cities, it remains the same as then left column, i.e., SAC Value.

Note: To apply conditional formatting, we need data. You can download the Power BI file to get the ready table.

You can download this Power BI SUMX Template here – Power BI SUMX Template

Things to Remember

  • SUMX function in power BI is used to calculate row by row calculations.
  • SUMX do the calculation as per the equation provided for Expression.
  • Each row will be affected by the SUMX function.

Recommended Articles

This has been a guide to SUMX in Power BI. Here we discuss how to use SUMX Function in Power BI along with examples and downloadable excel templates. You can learn more about Power BI form the following articles –

  • Types of Power BI Filters
  • Power BI GroupBy
  • Power BI Conditional Formatting Examples
  • Power BI Icons Sets
  • Power BI Calendar
0 Shares
Share
Tweet
Share
Power BI Training Certification
  • 6 Online Courses
  • 29+ Hours
  • Verifiable Certificate of Completion
  • Lifetime Access
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Download Power BI SUMX Template

By continuing above step, you agree to our Terms of Use and Privacy Policy.

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download Power BI SUMX Excel Template

Special Offer - Power BI Training Certification (22+ Hours of Training) View More