Product combinations by profit

[lauren777]

Hi there! 

I'm self taught at PBI and mostly I can get things by researching but this one has stumped me. 

I have a table similar to below which shows every product we stock, the cost and profit. What I'm wanting to do is have a table that shows the top combinations of products for profit depending on how much a customer wants to spend - limited to 2 or 3. Ie a jacket costs $60 and makes $30 profit but purchasing a shirt and a skirt costs $60 and makes $40 profit. 

Current data: 

Product	Cost	Profit
Shirt	$30	$20
Skirt	$20	$10
Jacket	$60	$30
Pants	$30	$20

Desired outcome data: 

The hope would be to have a report page where people can filter by max cost and get top product combinations to maximise profit.

Thank you in advance for any help 🙂

[technolog]

I hope this message finds you well. I've noticed that this solution remain unresolved. If any of you have managed to find a resolution to the issue, I kindly request that you share your solution for the benefit of the entire community. Alternatively, if you're still facing challenges, please do let us know as well.

Your insights and updates will greatly assist others who might be encountering the same challenge.

[OwenAuger]

Hi @lauren777 

From your description, it sounds like you want to solve the Knapsack problem, specifically the 0-1 Knapsack problem.

Just checking, is the below summary correct?

• You have a set of Products, with each Product having a Cost and Profit.
• For any given "maximum cost", you want to show the combination of Products that maximizes total Profit.
• When creating a combination of Products, each Product may appear at most once (e.g. you can't have 2 Shirts or 3 Skirts).
• You only want to consider combinations of up to a certain number of Products, say 3.

I'm thinking you could either

1. Pre-solve to find all profit-maximizing combinations at different cost levels (Power Query or elsewhere)
2. Calculate on the fly with DAX. A DAX approach might not be able to use one of the standard algorithms, but could possibly do some sort of enumeration of combinations.

Do you want to be able to apply filters in the report to limit the allowed Products?

Will post back with some further ideas 🙂

[lauren777]

@OwenAuger Thank you for your reply! 🙂 

The summary is correct! each Product could appear twice actually. To make it easier to explain, an electronic store is probably closer to the Products. The store could sell one speaker for $200 and make $80 profit, or they could sell 2 x hmdi cables, 1 x set top box and 2 x remotes for $200 and make $150 profit. I would say only consider up to say 3-4 products as majority of customers wouldn't go higher than that. 

I would like to have a filter applied on cost, stock category or stock levels at locations.

Hopefully this helps! I've seen someone do it with Python and was hoping to implement it into PowerBi.

[OwenAuger]

Hi again @lauren777 

Thanks for your patience! I was a little busy since your reply and you may well have looked at this yourself since then.

I have attached a PBIX with one possible approach to this.

DAX is not as powerful as general programming languages such as Python, so it is generally not possible to implement algorithms that involve looping (with variables that update in each iteration) or recursion. For example, we can't implement a dynamic programming algorithm or other standard knapsack problem algorithms.

The method I have used is to pre-compute a table containing all possible combinations of Products, subject to:

1. At most 4 distinct Products per combination.
2. At most 2 copies of each Product per combination.

This table is called CombinationProduct, and specifies, for each Combination ID, which Products are present and how many of each:

There are different ways this table could be generated, and you can see how I did it in Power Query (with parameters MaxRepetitions = 2 and MaxProducts = 4). If you update the Product table, the rest of the queries will update accordingly.

CombinationProduct is then related to the Product and Combination dimension tables:

The model also includes a Cost Limit parameter table, with a corresponding measure Cost Limit Value (created with Modeling > New Parameter > Numeric Range).

We can then write a measure that determines the Profit of the Combination that produces maximum Profit, subject to the Cost Limit. Here is a list of the relevant measures

Basic measures:

Product Cost = 
SUM ( 'Product'[Cost] )

Product Profit = 
SUM ( 'Product'[Profit] )

Combination Cost = 
SUMX (
    CombinationProduct, CombinationProduct[Count] * [Product Cost]
)

Combination Profit = 
SUMX (
    CombinationProduct, CombinationProduct[Count] * [Product Profit]
)

Top Combination Profit measure:

Top Combination Profit = 
VAR CostLimit = [Cost Limit Value]
VAR CombinationsFiltered =
    CALCULATETABLE (
        FILTER (
            ADDCOLUMNS (
                -- Combinations that exist for filtered Products
                SUMMARIZE ( CombinationProduct, Combination[Combination ID] ),
                "@Profit", [Combination Profit],
                "@Cost", [Combination Cost]
            ),
            -- Cost constraint
            [@Cost] <= CostLimit
        ),
        -- Overall CombinationProduct filter, includes Combination & Product in expanded table
        -- so that, if a visual is grouped by fields from CombinationProduct/Combination/Product,
        -- the values will be nonblank only for top Combination.
        ALLSELECTED ( CombinationProduct )
    )
    
VAR TopCombination =
    INDEX (
        1,
        CombinationsFiltered,
        -- Top combination by Profit, breaking ties by Combination ID
        ORDERBY ( [@Profit], DESC, Combination[Combination ID], ASC )
        -- omit other arguments
    )

VAR Result =
    CALCULATE (
        [Combination Profit],
        KEEPFILTERS ( TopCombination )
    )
RETURN
    Result

There is a similar Top Combination Cost measure too.

These measures can be placed in visuals that group by columns of Combination/CombinationProduct/Product.

Some notes:

1. By filtering on Product, we effectively end up with multiple identical Combinations, since we have excluded Products from Combinations that include other Products. The measure above breaks ties so that an individual Combination is selected at the end.
2. The number of combinations with up to 4 Products and at most 2 of any one Product follows this relationship (a 4th degree polynomial in the number of Products), so there could be a point at which this becomes too large.

Let me know if this is along the lines of what you were looking for. There are undoubtedly other approaches, such as pre-computing a Combination's total Profit/Cost, which I chose not to do.

Regards