Calculate Volume with multiple pack sizes of same item

[Qualube]

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.

[TomMartens]

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

[Qualube]

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.

[TomMartens]

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

[Qualube]

Blimey!!!

Have I got alot to learn, created the column but got a error.

[TomMartens]

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

[Qualube]

Thank you Tom

Really appreciate the help.

[TomMartens · Accepted solution]

Hey @Qualube,

please also consider to mark the apropriate post as answer, this will also help others.

Regards,

Tom