Help

Calculation Percent yes/no in one field

Topic Labels: Data Formulas Integrations
Solved
Jump to Solution
275 4
cancel
Showing results for 
Search instead for 
Did you mean: 
KevinTC91
4 - Data Explorer
4 - Data Explorer

I am looking for a way to calculate some percentages in my base. 

KevinTC91_0-1707511298379.png

There are a few things I need to do first:

1. I want to only include records listed as "Budgeted" or "Not Budgeted", not any "N/A" records. (this removes TEST 1 and TEST 4 from my calculations)

2. I also want to only include records with an authorized budget entered, so exclude any that the authorized budget=$0.00 (this removes TEST 2 and TEST 3 from my calculations)

3. Of those filtered to only budgeted or not budgeted and not $0.00, I want to count the ones with a remaining budget greater than 0, and count those with remaining budget less than zero, getting a percentage of the two. (in this table you can see there would be 50% on budget, 50% remaining budget is negative.

The end goal is to display the percent of records on budget, the percent of records over budget, and possibly the percent of records with no authorized budget entered. 

I know the process would involve another table with lookup fields and rollup fields but I can figure out the steps to make it work... 

 

1 Solution

Accepted Solutions

Ah, I think he may just want the count actually, and so we would need 'COUNT' fields to:
1. Get the total number of records that match the filter (Budget != N/A, Authorized != 0)

Screenshot 2024-02-10 at 11.08.35 AM.png
2. Get the total number of records over budget (Budget != N/A, Authorized != 0, Remaining < 0)

Screenshot 2024-02-10 at 11.08.55 AM.png
3. Get the total number of records within budget (Budget != N/A, Authorized != 0, Remaining > 0)
Screenshot 2024-02-10 at 11.08.45 AM.png

And use a formula field formatted to display as a percentage to get the percentages of both by just dividing them:

Screenshot 2024-02-10 at 11.10.05 AM.png

Link to base

See Solution in Thread

4 Replies 4
Dan_Montoya
Community Manager
Community Manager

Hi Kevin,

You do need to use a second table.  I like to call this type of table my "reports" table.  It will have 1 record in it called "Reports" and linked back to every project you have .

Once you have them linked, you'll build your percentages in your reports table.

Attached is the Budgeted roll up.  Repeat for Not Budgeted and N/A projects (if desired).

Next create your formula based on what you want to calculate (that was a little unclear from your explanation.  In this example, I've calculated the remaining budget as a percentage of the totals of the budgeted and unbudgeted.

Back in your projects table, create a lookup field from your Reporter field.  In my example I looked up the total amount budgeted ($500).  Now I can create a formula that calculates the % spent of the Total in the lookup field.

 

Ah, I think he may just want the count actually, and so we would need 'COUNT' fields to:
1. Get the total number of records that match the filter (Budget != N/A, Authorized != 0)

Screenshot 2024-02-10 at 11.08.35 AM.png
2. Get the total number of records over budget (Budget != N/A, Authorized != 0, Remaining < 0)

Screenshot 2024-02-10 at 11.08.55 AM.png
3. Get the total number of records within budget (Budget != N/A, Authorized != 0, Remaining > 0)
Screenshot 2024-02-10 at 11.08.45 AM.png

And use a formula field formatted to display as a percentage to get the percentages of both by just dividing them:

Screenshot 2024-02-10 at 11.10.05 AM.png

Link to base

KevinTC91
4 - Data Explorer
4 - Data Explorer

Thank you so much! I think I'm missing something here, but if I were to add more budget "TEST" records, how do I set them up to automatically link to the roll up to keep the percentage up to date?

Dan_Montoya
Community Manager
Community Manager

I use an automation with the condition "if field does not contain Reporter" then update the record to add Reporter .