Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Super User

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
Super User

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
Anonymous
Not applicable

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 🙂

Anonymous
Not applicable

@Anonymous 

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)

 

 

 

Anonymous
Not applicable

Hi @Anonymous,

 

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.