cancel
Showing results for
Did you mean:
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 Type Style Barcode Date Sales Value Sales Units S 505336637308 24/07/2018 89.16 2 S 505336771951 23/07/2018 52.01 1 S 505336771951 28/07/2018 44.58 1

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 Barcode Sales Value Sales Units Total Sales A 505336637308 89.16 2 178.32 505336771951 96.59 2 193.18 Total 185.75 4 743

`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 Barcode Sales Value Sales Units Total Sales B 505336637308 89.16 2 178.32 505336771951 96.59 2 96.59 Total 185.75 4 274.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 Barcode Sales Value Sales Units Total Sales A 505336637308 89.16 2 178.32 505336771951 96.59 2 193.18 Total 185.75 4 371.5

Any help would be very much appreciated.

Thank you

1 ACCEPTED SOLUTION
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:

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
5 REPLIES 5
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:

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
Resolver I

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 🙂

Solution Specialist

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)

Resolver I

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

 Style Barcode Sales Value Sales Units Total Sales A Sales Tot B Total Sales Calculated Column 505336637308 89.16 2 178.32 178.32 £178.32 505336771951 96.59 2 193.18 96.59 £96.59 (Wrong) £185.75 £4.00 £371.50 £274.91 £274.91
Super User II

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

Announcements

#### 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.

#### Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

#### Urdu Hindi D365 Bootcamp

Dont miss our very own April Dunnam’s The Developer Guide to the Galaxy! Find out what the Power Platform has to offer for the traditional developer.

Top Solution Authors
Top Kudoed Authors