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
jan_buzek
New Member

Combination of items to fill a basket closest to maximum volume

Hello,

 

I would like to fill baskets with products to make them as "full" as possible based on volume. For simplicity, lets ignore quantity and other parameters as I account for those elsewhere already.


I have a dataset that looks somewhat like this:

ProductVolume
A4
B3
C2,5
D2

 

Lets say there is a basket with maximum volume of 10. I need to make a calculation that would output the following resulting in an order of 9,5:

ProductVolumeOrder
A44
B33
C2,52,5
D2 

 

This should be done in DAX - probably using a calculated table?

 

Thank you for any suggestions!

 

1 ACCEPTED SOLUTION
v-cazheng-msft
Community Support
Community Support

Hi @jan_buzek 

You can create a Measure as the following.

 

Fill Basket =

VAR basket_size = 10

VAR total_vol =

    CALCULATE ( SUM ( 'Table'[Volume] ), ALL ( 'Table' ) )

VAR accmulate_vol =

    CALCULATE (

        SUM ( 'Table'[Volume] ),

        FILTER ( ALL ( 'Table' ), 'Table'[Volume] >= MAX ( 'Table'[Volume] ) )

    )

VAR inter =

    CALCULATE (

        SUM ( 'Table'[Volume] ),

        FILTER ( 'Table', accmulate_vol < basket_size )

    )

VAR res =

    IF ( HASONEVALUE ( 'Table'[Product] ), inter, basket_size )

RETURN

    IF ( total_vol <= basket_size, SUMX ( 'Table', 'Table'[Volume] ), res )

 

The result looks like this:

v-cazheng-msft_0-1619589429574.png

 

For more details, you can refer the attached pbix file.

 

Best Regards

Caiyun Zheng

 

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

 

View solution in original post

2 REPLIES 2
v-cazheng-msft
Community Support
Community Support

Hi @jan_buzek 

You can create a Measure as the following.

 

Fill Basket =

VAR basket_size = 10

VAR total_vol =

    CALCULATE ( SUM ( 'Table'[Volume] ), ALL ( 'Table' ) )

VAR accmulate_vol =

    CALCULATE (

        SUM ( 'Table'[Volume] ),

        FILTER ( ALL ( 'Table' ), 'Table'[Volume] >= MAX ( 'Table'[Volume] ) )

    )

VAR inter =

    CALCULATE (

        SUM ( 'Table'[Volume] ),

        FILTER ( 'Table', accmulate_vol < basket_size )

    )

VAR res =

    IF ( HASONEVALUE ( 'Table'[Product] ), inter, basket_size )

RETURN

    IF ( total_vol <= basket_size, SUMX ( 'Table', 'Table'[Volume] ), res )

 

The result looks like this:

v-cazheng-msft_0-1619589429574.png

 

For more details, you can refer the attached pbix file.

 

Best Regards

Caiyun Zheng

 

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

 

Fowmy
Super User
Super User

@jan_buzek 

I got the idea but your data is not as simple as this sample I believe. Can you share a sample that is much closer to the actual as I think the logic should be applicable at the end.

 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

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.