Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi
I have an issue that I am trying to solve with little success, I have an item code but it is sold in multipl pack sizes, eaches, Box (12) & Box (36).
The issue is I am trying to calculate the volume of the total sales of this product but my related table only has one value for volume which is an each.
I can't put multiple values for the volume against the item code as this would no longer be unique and I can't change the item code for the different sales units as they come straight from a SQL Server.
Is it possible to include a conditional IF statement with the additiona calculations?
Thanks in advance.
Solved! Go to Solution.
Hey @Qualube,
please also consider to mark the apropriate post as answer, this will also help others.
Regards,
Tom
Hey,
I have to admit that I have my difficulties to understand how your data looks like.
Please prepare a PBIX file that contains some sample data, upload the file to onedrive or dropbox and share the link.
It's also important to know the connection mode from Power BI to the SQL Server you mentioned is "Import" or "Direct Query".
Regards,
Tom
Hi Tom
Thanks for the reply the SQL format is import and the screenshot is below, the pack size id identical however the SALESUNIT is
different, currently everyting totals the each value of 0.4.
Hey,
create a calculated column like so
columnname = SWITCH( 'tablename'[SALESUNIT] ,"ea", 'tablename'[SALESUNIT] * 'tablename'[VOLUME] ,"Box (12)", 'tablename'[SALESUNIT] * 'tablename'[VOLUME] * 12 ,"Box (36)", 'tablename'[SALESUNIT] * 'tablename'[VOLUME] * 36 ,BLANK() )
Regards,
Tom
Blimey!!!
Have I got alot to learn, created the column but got a error.
Silly me,
typing from mobile is not that easy this should work now:
SWITCH( 'tablename'[SALESUNIT] ,"ea", 'tablename'[SALESQTY] * 'tablename'[VOLUME] ,"Box (12)", 'tablename'[SALESQTY] * 'tablename'[VOLUME] * 12 ,"Box (36)", 'tablename'[SALESQTY] * 'tablename'[VOLUME] * 36 ,BLANK() )
Please excuse.
Regards,
Tom
Thank you Tom
Really appreciate the help.
Hey @Qualube,
please also consider to mark the apropriate post as answer, this will also help others.
Regards,
Tom
User | Count |
---|---|
130 | |
108 | |
101 | |
67 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |