cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bchager6
Continued Contributor
Continued Contributor

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 ProjectProject AmountApproval Level
AreaBudget 
IT$1,000,000 
HR$2,000,000 
Sales$3,000,000 
   
Approval Levels
IT projects>$1,499,999IOC
">$1 and <= $1,499,999Local
non IT projects>=$2,500,000IOC
">=$2,000,000 and < $2,500,000Management
"<$2,000,000Local
2 ACCEPTED SOLUTIONS
elofstrom
Resolver II
Resolver II

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.
Eric
 
ApprovalLevels.PNG
 
 

View solution in original post

@bchager6 - 

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

 

View solution in original post

12 REPLIES 12
elofstrom
Resolver II
Resolver II

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.
Eric
 
ApprovalLevels.PNG
 
 
bchager6
Continued Contributor
Continued Contributor

@elofstromI 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?

 

Capture.JPG 

@bchager6 - 

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

 

bchager6
Continued Contributor
Continued Contributor

Brilliant. That did the trick. Thank you SO much!

Yes, each project ID can have multiple project names.

Happy to help!

Incidentally we could have also done ALLEXCEPT(Project[Project ID]) instead of the ALL(Project[Project Name]).  Hindsight being 20-20 you may want to use that in the event you add a separate column other than Project Name that can have different values within the same Project ID.

Eric

 

bchager6
Continued Contributor
Continued Contributor

Thank you again. You've been extremely helpful!

bchager6
Continued Contributor
Continued Contributor

Thanks 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

 

bchager6
Continued Contributor
Continued Contributor

Rookie question: Did you relate your approval level table to the source table?

Nope - it's just floating out there all by itself.  In fact you probably wouldn't want to so that any filters you applied to the budget table wouldn't flow thru the relationship to the approval level table.  And that M:M relationship could cause some headaches.

If this answered your question could you please mark as the Solution?

Thanks!

bchager6
Continued Contributor
Continued Contributor

It's going to take a few days before I get to try this out, but when I do, absolutely.

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors