There are all kinds of interesting situations that arise when we deal with variances, for example the different treatment for debit vs credit accounts, this year vs last year, or one version in the same year vs another version. This article is focused on what happens to variance percent’s when dealing with negative values in web reports.
The standard Difference element in web reporting does a straight mathematical variance calculation, it does have a check box to flip the signs for debit accounts, but it does not work right for variances when all the data is negative. You can see in the sample below that both Sales – North and Sales – South have a reduction year over year of 500,000, but the variance percent calculation displays Sales – North as a 50% improvement. It also incorrectly displays Sales – East as a 50% reduction year over year.
The reason for this is the formula for a mathematical variance for the above example is:
(FY 2020 – FY 2019)
This results in a calculation where -500,000 is divided into -1,000,000 (the denominator). While that is mathematically correct, it is not correct in a financial sense.
There is a simple solution to correct this situation. Use a Custom calculation element instead of the Difference element in your report. This allows you to define the calculation and add the extra smarts needed to deal with negative denominators. Here is an outline of the steps to get the correct variance percent for our example:
1. Modify the report
2. Select Calculations from the elements pane on the left of the screen
3. Select Custom and drag that onto the report to the right of the columns you want to calculate the variance percent for
4. Right click on the new column, select Formula Assistant
5. In the Formula Assistant you will see all the elements needed to build the following formula (the portions highlighted below were added by double clicking the related item in the lower right elements box, the rest was typed in):
6. Click OK to close the Formula Assistant
7. Right click on your column and select Properties
8. Update the Label field to display what you wish to call this column
9. Select the Percent radio button under Display As
10. Click Apply
11. Save and Run your report
In the result below you can see that the new column displays the variance percent’s as desired.