Forum Discussion
Measure to Show Only Projects that Fit Budget when Ranked
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.
9 Replies
- v-jiascu-msft
Microsoft Employee
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
Advocate I
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
Microsoft Employee
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