Skip to main content

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

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... 

 

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.


 

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)


2. Get the total number of records over budget (Budget != N/A, Authorized != 0, Remaining < 0)


3. Get the total number of records within budget (Budget != N/A, Authorized != 0, Remaining > 0)
 

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

Link to base

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?

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

Reply


Terms & Conditions