Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Qualube
Helper II
Helper II

Calculate Volume with multiple pack sizes of same item

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.

1 ACCEPTED SOLUTION

Hey @Qualube,

 

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

 

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

View solution in original post

7 REPLIES 7
TomMartens
Super User
Super User

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



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

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.

Capture.JPG

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

 

 

 

 



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

Blimey!!!

 

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

Capture.JPG

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

 

 



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

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



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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.