Forum Discussion
Calculated column that populates approval level based on project type and budget amount
All - I'm seeking assistance with creating a calculated column that may require storing values in variables, which is not something I have experience with.
I need to create a calculated column that first looks in the "Type of Project" column in the first table below to see if it's an IT or non-IT project. From there, apply the "Approval Levels" rules listed in the second table, and return the appropiate value (Local, IOC, or Management) in the "Approval Level" column in the first table.
Major Kudos to anyone who can help me here...
| Type of Project | Project Amount | Approval Level |
| Area | Budget | |
| IT | $1,000,000 | |
| HR | $2,000,000 | |
| Sales | $3,000,000 | |
| Approval Levels | ||
| IT projects | >$1,499,999 | IOC |
| " | >$1 and <= $1,499,999 | Local |
| non IT projects | >=$2,500,000 | IOC |
| " | >=$2,000,000 and < $2,500,000 | Management |
| " | <$2,000,000 | Local |
So what I did to solve this was to change the model of the "Approval Levels" table to be Project Area, Min Budget, Max Budget, Approval Level.
Once I had that then I created an "Approval Level" calculated measure in the Projects table with the following code (you may need to adjust to match your model):
Approval Level =IF(HASONEVALUE(Projects[Project Area]) && HASONEVALUE(Projects[ProjectID]),VAR _currentArea = IF(SELECTEDVALUE(Projects[Project Area]) = "IT", "IT", "Non-IT")VAR _currentBudget = SUM(Projects[Budget])VAR _Return = CALCULATE(MAX('Approval Levels'[Approval Level]), 'Approval Levels'[Project Area] = _currentArea, _currentBudget >= 'Approval Levels'[MinBudget], _currentBudget < 'Approval Levels'[MaxBudget])RETURN _Return,BLANK())Since the appropriate approval level will be returned if I'm looking at one project (i.e. you probably don't want to aggregate budgets over all projects and then look-up the approval level but if you have multiple budget lines for one project it'll aggregate those amounts) the HASONEVALUE check verifies that we're looking at one project and one project area, stores those two values in variables, and then the CALCULATE statement filters the Approval Levels table down to that row that matches the Project Area and the budget amount is between the Min and Max Budget fields. Since I know that I'm only looking at one row I can safely use MAX to retrieve the text value.I can't figure out how to include the sample PBIX I created to solve this - if someone knows how to attach a PBIX let me know and I'll upload. I've included a screenshot of the model and the DAX.Hope that helps.EricAnonymous -
Am I reading the data correctly that one Project ID (in this case 540) can have multiple Project Names? It looks like it has 4 separate names for that one Project ID. Or did you type in the 1, 2, 3, 4 for illustrative purposes?
If it can have multiple names for one ID try changing the code for _currentBudget (I think that's the variable - it's not showing me the original post) from SUM(Project[Budget]) to CALCULATE(SUM(Project[Budget], ALL(Project[Project Name])). I haven't tested that but that essentially "ignores" the project name but still honors the project ID.
I'll see if I can find my original PBIX and try to dummy it up.
Eric
12 Replies
So what I did to solve this was to change the model of the "Approval Levels" table to be Project Area, Min Budget, Max Budget, Approval Level.
Once I had that then I created an "Approval Level" calculated measure in the Projects table with the following code (you may need to adjust to match your model):
Approval Level =IF(HASONEVALUE(Projects[Project Area]) && HASONEVALUE(Projects[ProjectID]),VAR _currentArea = IF(SELECTEDVALUE(Projects[Project Area]) = "IT", "IT", "Non-IT")VAR _currentBudget = SUM(Projects[Budget])VAR _Return = CALCULATE(MAX('Approval Levels'[Approval Level]), 'Approval Levels'[Project Area] = _currentArea, _currentBudget >= 'Approval Levels'[MinBudget], _currentBudget < 'Approval Levels'[MaxBudget])RETURN _Return,BLANK())Since the appropriate approval level will be returned if I'm looking at one project (i.e. you probably don't want to aggregate budgets over all projects and then look-up the approval level but if you have multiple budget lines for one project it'll aggregate those amounts) the HASONEVALUE check verifies that we're looking at one project and one project area, stores those two values in variables, and then the CALCULATE statement filters the Approval Levels table down to that row that matches the Project Area and the budget amount is between the Min and Max Budget fields. Since I know that I'm only looking at one row I can safely use MAX to retrieve the text value.I can't figure out how to include the sample PBIX I created to solve this - if someone knows how to attach a PBIX let me know and I'll upload. I've included a screenshot of the model and the DAX.Hope that helps.EricThanks very much! Impressive, and fast!
I have a few quick questions: the budget numbers are already in the data. It looks like you are you saying that I should add the 2 additional columns to the data (MinBudget & MaxBudget) before I run the code?Why is "IT" listed twice in this line? VAR _currentArea = IF(SELECTEDVALUE(Projects[Project Area]) = "IT", "IT", "Non-IT")
The VAR _currentArea could've been written IF(SELECTEDVALUE(Projects[Project Area] = "IT", SELECTEDVALUE(Projects[Project Area]), "Non-IT") but I got lazy. :) I'm simply checking to see if I'm looking at a non-IT area for when we perform the "lookup" in the Approval Level table.
I'm not sure what your Approval Level table looks like (I'm assuming that it's a separate table). Mine looks like the table on the right in the screenshot. You simply want one column that has the numeric lower boundary of the approval level ladder and one column that has the upper boundary. Have one column that says ">0 and <1000000" won't work for the way I created the solution.
Hope that helps!
Eric
AnonymousI just did some further testing of this code. While it works on the row level, so to speak, it doesn't look to be aggregating the budget dollars across the same project ID. I would expect to see IAC level for each row in the below table because the second row takes the project in to that approval level. Something I noticed is that if I remove the Project Name from my table, the individual project amounts aggregate and the correct approval level is returned. That isn't an optimal solution though because I need to include the project name in my table. Your thoughts?
Anonymous -
Am I reading the data correctly that one Project ID (in this case 540) can have multiple Project Names? It looks like it has 4 separate names for that one Project ID. Or did you type in the 1, 2, 3, 4 for illustrative purposes?
If it can have multiple names for one ID try changing the code for _currentBudget (I think that's the variable - it's not showing me the original post) from SUM(Project[Budget]) to CALCULATE(SUM(Project[Budget], ALL(Project[Project Name])). I haven't tested that but that essentially "ignores" the project name but still honors the project ID.
I'll see if I can find my original PBIX and try to dummy it up.
Eric