EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials Power BI learn Power BI SUMX
 

Power BI SUMX

Priya Pedamkar
Article byPriya Pedamkar

Updated February 28, 2023

Power BI SUMX

 

 

SUMX in Power BI

The SUMX function in Power BI facilitates the sum of products. So, it first performs product of the corresponding field values for each row and then adds them. In Power BI, the function can be implemented either through a measure or through a column. However, the implementation is meaningful when it is applied in a measure.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

How to Implement the SUMX Function in Power BI?

In order to understand the concept of SUMX function in Power BI, we will go through a step by step procedure. For this demonstration, we are considering the Fleet dataset. The dataset contains data pertaining to average quantity supplied and a number of days operated in a year for various fleets. The important dimension here is Fleet id, while important measures are Quantity and Days. Using SUMX function, we intend to obtain the total quantity a fleet is accountable for in a year. Let’s go through the step-by-step procedure as given below.

To apply 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.

You can download this Power BI SUMX Excel Template here – Power BI SUMX Excel Template
  • Step 1: Now to create Parameters in Power BI, first we need to upload this data in Power BI. Go to the home menu and click on Get Data option to know the list of all the sources as shown below. As our data is in excel, so we will be selecting Excel.

Example 1 (Menu Ribbon)

  • Step 2: This will take us to browse window. Find the file and open it. From the navigator window, select the sheet which we want to use in Power BI and then click on Load.

 Example 2 (Navigator Window)

  • Step 3: When the file successfully gets loaded into Power BI, the dimensions and measures present in it can be seen in the Fields section.

Power BI SUMX Example 3

Step 4: Now, in the Visualizations section, click on the Table symbol highlighted by the red box.

Power BI SUMX Example 4

  • Step 5: Next, in the Fields section, select all the fields viz. dimension Days, and measures Fleet ID and Quantity. When they are selected, a table gets created as can be seen on the left-hand side of the screenshot.

Power BI SUMX Example 5

  • Step 6: The text size in the table in the above screenshot is very small. To increase the size, select ‘Actual Size’ option (View -> Page View -> Actual Size). As a result, we get a table with fairly good text size.

Power BI SUMX Example 6

  • Step 7: Now, we intend to bring the total quantity into the table. The total quantity will be the product of Quantity and Days measures. We want this for each fleet. This can be accomplished using the SUMX function. We first need to create a measure. So, click on ‘More options’ for the ‘Fleet Data’ table in the Fields section, and click on ‘New measure’.

 Example 7 (New Measure)

  • Step 8: When we click on ‘New measure’, we are navigated to the Expressions bar. The default name of the measure is Measure.

Power BI SUMX Example 8

  • Step 9: Change the name of the measure to Total Quantity as shown by the following screenshot.

Example 9 (Total Quantity)

  • Step 10: Just start typing sumx as shown below and Power BI automatically gives us detailed information pertaining to parameters etc.

Power BI SUMX Example 10

  • Step 11: As we move inside the function, we find the Table as the first parameter. Mention here the name of the concerned table.

Power BI SUMX Example 11

  • Step 12: Now, in this case, we have only one table which is ‘Fleet Data’. So, just press f, and we find the table in the list. Move to the table name, and press ‘Enter’, it becomes the first parameter.

Power BI SUMX Example 12

  • Step 13: Once we are done with specifying the table name which is the first parameter in SUMX function, type comma, and the next parameter i.e. Expression gets activated. It is very important part of the SUMX function.

Power BI SUMX Example 13

  • Step 14: Observe carefully the Expression in the SUMX function in the following screenshot. Our expression is basically a product of Quantity and Days.

Power BI SUMX Example 14

  • Step 15: When the SUMX function is correctly implemented using the above steps, then the Total Quantity measure gets created as can be seen in the Fields section.

Example 15 (Fields section)

  • Step 16: Select the Total Quantity, and the table gets created.

 Example 16 (Total Quantity)

  • Step 17: The following table gives a closer look into the Total Quantity. As we can see, the measure has successfully given the correct figures. This is because we performed SUMX over the measure. Had we performed it over the column, then for each Fleet ID, we would have got the total sum of the products as against the sum of products for each row.

Power BI SUMX Example 17

  • Step 18: Now, another field ‘Quantity Loss’ gets added into the data source. This time the net quantity will have to take into consideration. The formula shall be the product of days and quantity excluding loss. Load the updated sheet “Fleet Data Updated”, by following the procedure mentioned in the above section. The following screenshot shows the updated dataset loaded into Power BI.

Power BI SUMX Example 18

  • Step 19: Have a look at the table after the ‘Quantity Loss’ field is brought into it as shown below.

Example 19 (Quantity Loss)

  • Step 20: As shown below, we implemented the SUMX expression to incorporate the impact of quantity loss.

Power BI SUMX Example 20

  • Step 21: Observe carefully the SUMX expression in the following screenshot. Just check how it works.

Power BI SUMX Example 21

  • Step 22: Now, in the Fields section, select Net Quantity.

 Example 22 (Fields section)

The SUMX function does its work by giving us the correct result.

Correct Result

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

  • The SUMX function should be implemented through measure and not through column, otherwise, the same result will be obtained for each row-to-row combination.
  • SUMX is different from SUM because the SUM function just adds all the column values.
  • Implementation of the SUMX function requires knowledge of DAX language.

Recommended Articles

This is a guide to Power BI SUMX. Here we discuss how to Implement the SUMX Function with an example and downloadable template. You may also look at the following articles to learn more –

  1. How to Create Charts in Power BI
  2. How to Design Pie Chart in Power BI
  3. Power BI Connections|Top 6
  4. Power BI Drillthrough | Excel Template
  5. Amazing 8 Power BI Alternatives

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

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

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

EDUCBA

Download Power BI SUMX Template

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

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

Forgot Password?

EDUCBA

Download Power BI SUMX Template

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

🚀 Limited Time Offer! - ENROLL NOW