Percentages can be used to gain valuable insight into the affects a portion has on a total. They’re heavily relied upon to determine and evaluate KPIs for an organization and guide operations to improve your overall business performance.

In this example, using the Jira Issue table within the Atlassian Data Lake data source, we divide our resolved issues over our total issues to calculate our resolution rate for the past 3 months.

1. First, create a query to calculate the number of issues that have been created. To do this, count the distinct number of Issue Key where Created At is in the last 3 months.

2. Rename the column to “Total issues” for clarity.

3. Now add another query to get the count of issues that have been resolved. To achieve this, count the distinct number of Issue Key where both Created At is in the last 3 months and Resolution At is not null.

4. Rename the column to “Resolved issues” for clarity.

5. After creating the two queries, make sure to use a cross join when merging them.

6. Add a "Formula column" step, choose Custom for the formula type, then enter the following percentage formula:

100.0 * "Resolved issues" / "Total issues" 

If you want to round your percentage to two decimal points, you can also use the SQLite ROUND() function; the formula will look like this, where 2 is the number of decimal places:

ROUND(100.0 * "Resolved issues" / "Total issues",2) 

Click Save to calculate the percentage.

7. Rename the Custom formula column to “Resolution rate” for clarity.

The final result set should look like this:

Troubleshoot Percent Change 

If you’re using the Percent change formula in your “Formula column” step, you may notice that if the previous row is 0, the percent change returns a NULL result. Don't worry—this is expected, and there are ways to address this.

Our Visual SQL steps run on SQLite, which adheres to Postel’s Rule. The default behavior when dividing by zero is to return NULL rather than throw an error when dividing by zero (see SQLite documentation for more information). This lines up with the generally accepted principle that the concept of percentage change has essentially no meaning when starting from zero.

There are several articles online showing potential workarounds. In Atlassian Analytics, however, one option is to add a "Apply formula" step after the percent change to denote the percent change doesn’t apply here. You’d select a Custom formula and add the following CASE statement to it, replacing Percent Change Issues created with the name of your column:

CASE WHEN "Percent Change Issues created" IS NULL THEN 'N/A' ELSE "Percentage Change Issues created" END