Showing $$ Needed to Reach Goal

[Presto_Dryclean]

Hi! Using the Summary Block, Under Summary Type, we will be able to get the sum of the deals we have won.

However, what we want to display on the summary block is the amount needed for us to achieve the goal for the year/ month. Is it possible to display $X - instead of just ?

Thanks!

[ScottWorld]

Hmmm… I’m actually not sure how to do that off the top of my head. I’m sure that there is probably a simple solution out there, but I can’t think of one at the moment.

It sounds like you are trying to display how much money is remaining for you to reach your goal. So, for example, if your goal is $100 and you’ve achieved $80 so far, you want the Summary Block to display “$20 left towards the goal”.

It’s almost like you want to use the Countdown Block, but you can’t use the Countdown Block because it only supports dates instead of numbers.

However, what DID pop into my head was something slightly different. You could show what percentage of the way that you have reached towards your goal so far.

So, for example, if you’ve achieved $80 of your $100 goal, you could have the Summary Block display “80% of the way towards our goal”.

To do this, you would create a formula field that figures out what percentage of the way each record is towards your goal. You would predefine this “goal number” within the formula, and you would format the formula field as a percentage.

Then, within the Summary Block, you would add up all of these percentages.

So, the end result would look something like this:

[Presto_Dryclean]

Thanks for the solution! Yes that will give some perspective to the team. But I think absolute numbers will still be a better way to showcase the effort required to reach the goal.

But I will definitely implement this as a intermediate solution. Thanks again.

[ScottWorld]

You’re welcome! :slightly_smiling_face:

I actually DO know how to accomplish this by using an additional table, but I was trying to solve it for you with just a single table… which I don’t think can be done in Airtable.

The way to accomplish this with an additional table is to simply link all of your relevant sales records from your “Sales” table to a SINGLE RECORD in a new table called “Monthly Goals”.

So, for example, for all your sales in your “Sales” table that are part of your May 2020 sales goal, link all of those sales records to the exact same record in your “Monthly Goals” table called “May 2020”.

Likewise, for June 2020, all of your sales in your “Sales” table that are part of your June 2020 sales goal would be linked to the same exact record in your “Monthly Goals” table called “June 2020”.

Same thing for each month.

(Note that you will always need to manually link each one of your sales records to your monthly goals table, because there is no automatic linking in Airtable.)

The reason we need to use another table is because summary functions are only available to us when using Rollup fields or Count fields.

So, in that new “Monthly Goals” table, you would create a Rollup field called “Current Sales Total” with the formula:

SUM(values)

This would give you the current running total of all the sales for each month’s linked sales.

Then, still in the “Monthly Goals” table, you would create a new currency field called “Total Sales Goal” where you manually type in the sales goal for each month.

Then, still in the “Monthly Goals” table, you would create a very simple formula field called “Amount Left Towards Goal” that would simply be the “Total Sales Goal” minus the “Current Sales Total”.

Then, create a new filtered view in your “Monthly Goals” table that ONLY shows you the CURRENT MONTH that you are tracking. You would need to manually change this filter every month.

Create your Summary Block based on this new filtered view, and display the “Amount Left Towards Goal” field.

Manually type the name of the month onto your Summary Block for everyone to see, which will also give you a visual reminder to yourself to change the filter every month.

That will do the trick for you! :slightly_smiling_face: