Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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
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
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
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 @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 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 |
Hey,
try the solution I creaed, it looks pretty close to what you are looking for at least to me.
Regards,
Tom
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
117 | |
101 | |
71 | |
61 |