cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
General
Frequent Visitor

Sumx totals wrong

Hi there I have a problem regarding the formula Sumx. Thing is that I need to use sumx because I want to multiply it with a realted table. So measure would be like Result = sumx(tablex, [measure] * RELATED(tableY;Column A)

 

The Measure part is what I have a question for. the attached Power pivot table shows the example. Measure 2 and 4 are identical but they come up with different numbers?

Why is this happening only when all columns are identical?

 

Table is this:

 

Table 1..pngTable 2.png

 

So table 13 sumx gives another result that sumx in table 1.

 

Please let me know why?

 

THanks

 

 

 

 

1 ACCEPTED SOLUTION
v-zhenbw-msft
Community Support
Community Support

Hi @General ,

 

We think the step that caused the difference is that Table 13 has no column8.

 

Because SUM() is an aggregator function.  It adds up all the values in a single column you specify after applying all filters that will impact the formula. 

SUMX() is an iterator function.  It works through a table, row by row to complete the evaluation after applying all filters.

The Table13 doesn’t have a column to distinguish each row, so when we use the SUM function, it will calculate the total and put it in each row. Then we use the SUMX function, it will iterate each row and add total together.

 

SUMX 1.jpg

 

You also can refer to the following two great articles.

https://exceleratorbi.com.au/use-sum-vs-sumx/

https://radacad.com/sum-vs-sumx-what-is-the-difference-of-the-two-dax-functions-in-power-bi

 

If you have any question, please kindly ask here and we will try to resolve it.

BTW, pbix as attached.

 

Best regards,

 

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
v-zhenbw-msft
Community Support
Community Support

Hi @General ,

 

We think the step that caused the difference is that Table 13 has no column8.

 

Because SUM() is an aggregator function.  It adds up all the values in a single column you specify after applying all filters that will impact the formula. 

SUMX() is an iterator function.  It works through a table, row by row to complete the evaluation after applying all filters.

The Table13 doesn’t have a column to distinguish each row, so when we use the SUM function, it will calculate the total and put it in each row. Then we use the SUMX function, it will iterate each row and add total together.

 

SUMX 1.jpg

 

You also can refer to the following two great articles.

https://exceleratorbi.com.au/use-sum-vs-sumx/

https://radacad.com/sum-vs-sumx-what-is-the-difference-of-the-two-dax-functions-in-power-bi

 

If you have any question, please kindly ask here and we will try to resolve it.

BTW, pbix as attached.

 

Best regards,

 

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

amitchandak
Super User
Super User

@General , what is the definition of the Measure 

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

HI measure definition is: SUM(table1 or 13; column9)

General
Frequent Visitor

Okay - thanks for the solution. This means I need to create a coloumn with a random value in each row to make sumx work. Great stuff. Have a nice day.

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Top Solution Authors
Top Kudoed Authors