cancel
Showing results for
Did you mean:
Laz Member

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

Accepted Solutions
Highlighted Super User

## Re: Help with SUMX: Calculating Total Sales

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

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
5 REPLIES 5
mnayar Established Member

## Re: Help with SUMX: Calculating Total Sales

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)

Highlighted Super User

## Re: Help with SUMX: Calculating Total Sales

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

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
Laz Member

## Re: Help with SUMX: Calculating Total Sales

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

## Re: Help with SUMX: Calculating Total Sales

Hey,

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

Regards,

Tom

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
Laz Member

## Re: Help with SUMX: Calculating Total Sales

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 