Formulas With Percentages
An introduction to formulas that calculate percentage values.
Any calculations with numbers can be turned into a percentage. To see your current status – how close you are to reaching 100% – you would usually divide the actual value by your target value and change the number format to percent in the formula editor.
When it comes to targets, you can use either static targets or dynamic targets.
- A static target is a fixed number you enter manually, and it doesn't change unless you open the formula and change it. If you want to use a static target, you will create a static number component and enter your desired target value. Example: Number of booked meetings ➗ 40
- A dynamic target is a calculation that uses data from a data source. Dynamic targets update whenever your data updates. If you want to use a dynamic target, you will either create a data function component that calculates your target value or use an existing formula component. Example: Number of booked meetings ➗ Number of new leads
Note on static targets
Be mindful of how you choose the static targets and what dashboard widgets you're going to use. Some widgets display KPIs for all employees together (number box, speedometer), and some can list each employee separately (table, timeline). If you choose a static target such as 40, it will stay the same no matter if you're showing team or employee KPIs.
Percentage of change allows you to see how a new value is performing in comparison to an old value. For example, with this formula, you could see that you've sold 75% more licenses this month compared to the previous month. The basic equation goes like this:
(New Value ➖ Old Value) ➗ Old Value
👉 Change the number format to Percent.
Here's an example:
- Licenses sold in June: 240
- Licenses sold in July: 420
- Percentage of change = (420 – 240)/240 = 75%
How to build the formula?
Calculating the current value is simple – you open the formula editor, add a data function component and fill in the required fields. Now, how do we get the old value in the same formula? – to get the old value, duplicate the component and add a history modifier to the duplicate. If you want to read more about the history modifier, here's the article.
The image below represents a formula that calculates sales growth compared to the previous month. Because there are a lot of different date fields in the data source, the formula uses date modifiers to get the right metrics. Learn more about date modifiers.
What's important is the scope of the history modifier needs to match the time period on the widget. So, if you want to see the monthly growth, the history modifier and widget's time period should be set to month. If you want to track the weekly growth, they should be set to week, and so on.
Note on formula preview
The preview in the formula editor is just a preview – it does not change or affect a widget's time period.
And here's how the percentage of change formula looks in the Advanced editor:
( ( Count(Adversus Sales,State="success").Date(Closed Time) - Count(Adversus Sales,State="success").Date(Closed Time).History(1,Month) ) / Count(Adversus Sales,State="success").Date(Closed Time).History(1,Month) )
Win rate shows what percentage of sales deals are closed won. Keeping track of the win rate allows your sales teams to better understand their progress and see if the current practices work well on the target market. There are different ways how to calculate the win rate – as always, that depends on your business needs. The example below shows how to create a win rate based on the following equation:
Number of Won Deals➗ (Number of Won Deals ➕ Number of Lost Deals)
👉 Change the number format to Percent.
You can always adjust the formula and add more variables depending on your needs. Here's an example of how this basic win rate looks using data from Zoho CRM.
And here's how the win rate formula looks in the Advanced editor:
( Count(Zoho CRM Deals,Stage="Closed Won").Date(Closing Date) / ( Count(Zoho CRM Deals,Stage="Closed Won").Date(Closing Date) + Count(Zoho CRM Deals,Stage="Closed Lost").Date(Closing Date) ) )
Was this article helpful?
Please leave a comment to help us improve.