Return rows based on the subtotal of one column, sorted by another

[dscain]

Hello,

I have a data set of a list of projects with Risk Priority and Project Cost columns. Given a budget amount, I would like to return a list of projects that could be completed, starting with the highest Risk Priority. 

For example, if I have the following data:

Project	Risk	Cost
1	45	1000
2	42	5000
3	35	8000
4	10	2000
5	5	5000

Setting a "Budget" slicer to 7000 would return the first 2 rows, 15000 would return the first 3 rows, etc.

Any help is appreciated, thank you!!

[Greg_Deckler]

@dscain So, you need to implement a while loop in DAX, something like:

Measure = 
  VAR __Budget = 7000
  VAR __Count = COUNTROWS('Table')
  VAR __ProjectTable = 'Table'
  VAR __Table = 
    ADDCOLUMNS(
      __Table,
      "__Budget",__Budget - SUMX(FILTER(__ProjectTable, [Risk] >= EARLIER([Risk])),[Cost])
    )
  VAR __Projects = FILTER(__Table, [__Budget] >= 0)
RETURN
  CONCATENATEX(__Projects,[Project],",")

[dscain]

@Greg_Deckler 

Sorry, I mistyped in the original post. I don't want to return the project numbers, but instead filter the table visual down to the viable projects based on the given budget, starting with the highest risk.

Regarding the suggestion above, how do I get 

 SUMX(FILTER(__ProjectTable, [Risk] >= EARLIER([Risk]))

to handle ties (projects with the same risk)?

Thank you!

[Greg_Deckler]

@dscain Right, so same technique but use the __Projects table VAR as part of a Complex Selector. You can use the IN operator for this. So grab the MAX project ID and then use IF(__ProjectID IN __Projects, 1, 0) then filter on 1. For ties, depends on what you want. If there are 2 risks that are the same value, the current version will choose the project based on the sort order in the table in the event that both projects cannot be funded. What do you want to happen? Neither be included? 

[dscain]

Here is what I have so far, I apologize if I'm being dense here 😞

ProjectInBudget = 
VAR _Budget = 1000000
VAR _Count = COUNTROWS(workorder)
VAR _ProjectTable = 'workorder'
VAR _Table = 
    ADDCOLUMNS(_ProjectTable,
        "_InBudget",_Budget- SUMX(FILTER(_ProjectTable,[Risk Rank Break Tie]>=EARLIER([Risk Rank Break Tie])),[costest]))
VAR ProjectID = MAX([wonum])
VAR _Projects = FILTER(_Table,[_InBudget]>= 0)
VAR _viable = IF([wonum] in _Projects,1,0)
return
_viable

I figured out the tie breaker portion by using the RAND() function in conjunction with RANKX() to get a unique rank for each project. 

When I try this code though, I get error "Function CONTAINSROW must have a value for each column in the table expression."

Looking at this code though, it seems like I would just want to do an IF statement to check if _InBudget > 0 and return all projects where True. However, when I tried that everything resolved to True. 

[Greg_Deckler · Accepted solution]

@dscain Right, you need a SELECTCOLUMNS statement:

ProjectInBudget = 
VAR _Budget = 1000000
VAR _Count = COUNTROWS(workorder)
VAR _ProjectTable = 'workorder'
VAR _Table = 
    ADDCOLUMNS(_ProjectTable,
        "_InBudget",_Budget- SUMX(FILTER(_ProjectTable,[Risk Rank Break Tie]>=EARLIER([Risk Rank Break Tie])),[costest]))
VAR ProjectID = MAX([wonum])
VAR _Projects = SELECTCOLUMNS(FILTER(_Table,[_InBudget]>= 0),"_wonum",[wonum])
VAR _viable = IF([wonum] in _Projects,1,0)
return
_viable

I *think* that should be correct. Basically you need a single column table of your project ids (wonum?) to use in your IN statement.

[dscain]

@Greg_Deckler  - I ended up working around this by creating a running sum column, then using that calculated column as a slicer, I think I was over-complicating things in my original thinking. However, I am going to run through this solution as well and come back to Accept as Solution. Thank you so much for your help, I definitely learned a lot! I came across your article on For/While loops in Power BI that was very helpful as well 🙂

[dscain]

@Greg_Deckler  - forgot to tag you on above reply