cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Laz
Resolver I
Resolver I

Help with SUMX: Calculating Total Sales

Hi Everyone,

 

Okay so i have the following table (SALES) which i am importing in to PowerBI using Power Query:

 

Ret Trans TypeStyle BarcodeDateSales ValueSales Units
S50533663730824/07/201889.162
S50533677195123/07/201852.011
S50533677195128/07/201844.581

 

So now that i have imported my data, what i must do is Multiply the Sales Value by the Sales Units: (Sales Value * Sales Units).

 

Easy stuff, i create a calculated measure and get the following:

 

Style BarcodeSales ValueSales UnitsTotal Sales A
50533663730889.162178.32
50533677195196.592193.18
Total185.754743

 

 

 

Sales Tot A = SUM(SALES[[Measures]].[Sales Value]]]) * SUM(SALES[[Measures]].[Sales Units]]])  

 

 

However the Down Total is incorrect as i dont want it to be multipled on row basis, but instead the column 'Sales Total A' to be added from a colum prespective.

 

So isntead i use a SUMX in order to resolve the above and get the below result:

 

 

Style BarcodeSales ValueSales UnitsTotal Sales B
50533663730889.162178.32
50533677195196.59296.59
Total185.754274.91

 

 

 

Sales Tot B = SUMX(SALES, SALES[[Measures]].[Sales Value]]] * (SALES[[Measures]].[Sales Units]]]))

 

However, the issue i am experiencing now is that even though the Down Total is working as expected from the SUMX, the second record for 'Total Sales B' is returning the incorrect value: 96.59 * 2 IS NOT CORRECT.

 

I undertand that SUMX goes on a row by row basis to calculate, therefore as the data imported has two seperate records for barcode 505336771951 with 1 Unit each, i am assuming the reason i am getting unexpcted result is becuase SUMX is doing: 52.01 * 1 + 44.58 * 1 = 96.59.

 

The result i am trying to achieve is the following:

 

 

Style BarcodeSales ValueSales UnitsTotal Sales A
50533663730889.162178.32
50533677195196.592193.18
Total185.754371.5

 

 

Any help would be very much appreciated.

Thank you

1 ACCEPTED SOLUTION
TomMartens
Super User II
Super User II

Hey,

 

first I created these two measures

 

Total Sales Units = 
SUM('Table2'[Sales Units]) 

and

 

 

Total Sales Value = 
SUM('Table2'[Sales Value]) 

Then I created this measure

 

Total Sale SUMX = 
SUMX(
    VALUES(Table2[Style Barcode])
    ,[Total Sales Units] * [Total Sales Value]
) 

Instead of itereating across the table I'm iterating across the filtered barcode(s)

 

I guess this returns what you are looking for:

image.png

 

Regards,

Tom

 

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

5 REPLIES 5
TomMartens
Super User II
Super User II

Hey,

 

first I created these two measures

 

Total Sales Units = 
SUM('Table2'[Sales Units]) 

and

 

 

Total Sales Value = 
SUM('Table2'[Sales Value]) 

Then I created this measure

 

Total Sale SUMX = 
SUMX(
    VALUES(Table2[Style Barcode])
    ,[Total Sales Units] * [Total Sales Value]
) 

Instead of itereating across the table I'm iterating across the filtered barcode(s)

 

I guess this returns what you are looking for:

image.png

 

Regards,

Tom

 

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

You are a legend @TomMartens

 

"Instead of itereating across the table I'm iterating across the filtered barcode(s)"... Makes Total Sense.

 

Thanks for the quick respon 🙂

mnayar
Solution Specialist
Solution Specialist

@Laz 

I am not sure if I completely understand your math. Is sales value the unit price or the final price? If it is not unit price, is the unit price sales value/unit price?

 

Also your SALES TOT A formula seems to be wrong if Sales Value is the unit price

 

you need to create a calc col which is sales value * units

and then just sum that and you will get the required ans.

 

Sales Tot A = SUM(SALES[[Measures]].[Sales Value]]]) * SUM(SALES[[Measures]].[Sales Units]]])  

 

your sales tot A formula is summing the sales value of all products (89+52+44) and sum of all units(2+1+1) and multiplying this which is wrong. it should be sum(sales values * units)

 

 

 

Hi @mnayar,

 

I had already attempted to create a Calculated Column, however, the second record would be incorrect that way.

 

This is is again due to having two seperarte records for Barcode: *1951 each having 1 Unit

 

Thanks for your responce 🙂

 

Style BarcodeSales ValueSales UnitsTotal Sales ASales Tot BTotal Sales Calculated Column
50533663730889.162178.32178.32£178.32
50533677195196.592193.1896.59£96.59 (Wrong)
 £185.75£4.00£371.50£274.91£274.91

Hey,

 

try the solution I creaed, it looks pretty close to what you are looking for at least to me.

 

Regards,

Tom 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors