topic Re: Maximize sum of (n-combination) rows with constraint in DAX Commands and Tips

Maximize sum of (n-combination) rows with constraint

stats — Wed, 03 Aug 2022 16:46:23 GMT

I have a table with 3 columns: Product, Cost, Revenue.

There are 1000 rows (1000 distinct Products).

My budget equals B.

I want to filter 3 Products so that I maximize the Revenue sum for those 3 products, provided that the total Cost for those 3 Products are less than my budget B.

Can this be done in Power BI?

I would appreciate any kind of help.
Thank you in advance.
Best Regards.

Re: Maximize sum of (n-combination) rows with constraint

daXtreme — Thu, 04 Aug 2022 06:52:10 GMT

stats

Such tasks are not suitable for effective DAX calculations (DAX is not a language for optimization problems). What you can do is this. Create a table where you'll have combinations of all 3 products. For each combination you should store their total cost and their total revenue as well Yes, I know, it'll be 1000^3 rows but PBI should be able to handle this, especially that there'll be a lot of repetition in each column (so the compression rate should be high). You can at least try... If you have such a table, it should then be easy to find the combination of interest.

Re: Maximize sum of (n-combination) rows with constraint

daXtreme — Thu, 04 Aug 2022 08:02:19 GMT

stats

Well, actually it'll not be 1000^3 combinations but only 166 167 000 (1000 over 3). Well, this is something PQ can definitely handle without a problem! 🙂