Measure to Show Only Projects that Fit Budget when Ranked

[JTPorterfield]

Hey all, on another post, I posted a really long winded explanation of a huge problem that I was fortunate to have help from user Greg_Deckler in figuring out.  In discovering the way forward, I thought I'd post a specific issue now as that is what is really holding me up.

Essentially, I need to create a project list that will update dynamically using a What If parameter.  The projects are all ranked sequentially.  The What-If parameter is for an annual budget amount that is input by the end user. The project list then needs to update to show all the projects which will "fit" that budget.  I've created this functionality using calculated columns, but those don't update dynamically using What-If parameters (which I think is based on speed, but it'd be nice to be able to control this as needed), so I used Query parameters when doing that.  However, this creates many problems for the end user, so we're trying to rework to avoid these issues.

Below is an example showing the results I would like to achieve.  Note that there may be gaps in the project list as a lower priority project may fit the budget after a higher priority project didn't fit (see column D, rows 13 & 14 below).

Any help would be greatly appreciated.

[v-jiascu-msft]

Hi @JTPorterfield,

Please check out the demo in the attachment. You can use a measure like below.

Measure =
VAR runningTotal =
    CALCULATE (
        SUM ( Table1[Poject Cost] ),
        FILTER ( ALL ( Table1 ), Table1[Index] <= MAX ( 'Table1'[Index] ) )
    )
RETURN
    IF ( runningTotal <= Parameter[Parameter Value], runningTotal, BLANK () )

Best Regards,
Dale

[JTPorterfield]

@v-jiascu-msft Dale, appreciate the help.  
However, this doesn't end up showing non-sequential projects that fit the budget.  Note that at parameter = 130, project rank 11 doesn't "fit" the budget, but project rank 12 should fit. Is there another way to make this happen? I may have to do some additional measures to bring it all together.

[v-jiascu-msft]

Hi @JTPorterfield,

Can you share a more complete sample, please? If they are non-sequential, how can we accumulate them? Why should project rank 12 fit?

Best Regards,
Dale

[JTPorterfield]

If you look at the math, you see that when the parameter is 130, after project rank 10, the running total is 119.  Then, project rank 11 at a cost of 15 doesn't fit (119+15>130), however, project rank 12 at cost of 11 does fit (119+11<130).  That's what I was trying to describe in the last paragraph of my original post.  Sorry - I could have been more clear about that.

[v-jiascu-msft]

Hi @JTPorterfield,

Why do we stop at 119 then find the proper ones to make it less than 130? Why not 94? If this is what you want, I'm afraid it's too complicated. What if the 11 is 1, should we check the others till the total is bigger than 130? 

Best Regards,
Dale

[JTPorterfield]

Hi @v-jiascu-msft

The objective is to perform the maximum number of ranked projects that will fit in a given budget.  This type of problem is fairly common when dealing with large sets of projects and limited funds. Essentially, you have more projects than you have money for, and you're trying to figure out which projects will fit a given budget amount.  First, you have to prioritize them based on whatever variable is important/relevant. Then, the desired functionality is that as you change the budget amount, the projects you should perform will update.  Then, you can look at the long-term impacts of different budget amounts and ideally find the best budget amount for the given set of projects.

You stop at 119 first because those are the most important projects that will all fit.  If the 11 was a one, then yes, you would continue to look for additional projects that are possible.

I think I've found a measure solution that works in the context of a table, though I'm having trouble getting it to work in other formats.  It involves a few measures.

First, I use VAR to create a running total, and then if the running total is less than the budget value,   This tells me initial projects that fit the budget.

Second, I identify additional projects that are smaller than the difference between the maximum running total and the annual budget.

Finally, I use a similar format to the initial running total, but filtered for only projects that initially fit the budget or those additional projects.  This seems to work, but again, only in the context of a table, so I'm still working on trying to visualize it in a graph.

[v-jiascu-msft]

Hi @JTPorterfield,

Do you mean you can do it with a calculated column but not with a measure? Can you share it?

Best Regards,
Dale

[JTPorterfield]

@v-jiascu-msft,

I posted the formulas I used to use calculated columns in message 9 of this post - https://community.powerbi.com/t5/Desktop/Issues-with-Query-Parameters-Needing-to-Refresh-Data-Source...

I've also found a measure solution, but it only works when used in a table, which is only part of what I'm looking for.  I've attached your original PBIX with the measures added, and a few more rows of data.

*edit - well, I thought I attached it, but apparently I don't have access to do so.

[v-jiascu-msft]

Hi @JTPorterfield,

You can upload the file to a cloud drive like OneDrive, GoogleDrive then share the download link here. Please mask the sensitive parts first.

Best Regards,
Dale