Cross-Row Calculation of Growth Rate

Background

This article introduces how to calculate the sales growth rate of one year compared to the given year.

For example, to calculate the sales growth rate of 2014, 2015, and 2016 compared to 2013, cross-row calculation can be used.

Although this type of calculation cannot be directly achieved in FineBI 5.0, the DEF function in FineBI 6.0 can meet this requirement.

Preview

For online preview, click Cross-Row Calculation of Growth Rate.

You can click Save as subject to make this chart by yourself or view the editing steps.

Adding Data

Sample data: Supermarket Sales Data.xlsx

Create a subject and upload the sample data.

Creating a Component

To calculate the sales growth rate of 2014, 2015, and 2016 compared to 2013, use the formula: annual sales / sales of 2013 - 1.

Calculating the Sales of 2013

1. To extract the year from the date, you can add a calculation indicator named Year, as shown in the following figure.

For details about the YEAR function, see YEAR.

2. Calculate the sales volume of 2013 with the DEF function, as shown below.

Calculating the Growth Rate Compared to 2013

Use the formula annual sales / sales of 2013 - 1 to calculate the growth rate compared to 2013.

Add a calculation indicator and enter the formula SUM_AGG(Sales Volume)/SUM_AGG(Sales of 2013) - 1, as shown below.

In this way, you can get the growth rate of each year compared to 2013.

Creating a Table

Set the chart type as Group Table, and drag Time of Order into Dimensions and Sales Volume, Sales of 2013, Growth Compared to 2013 into Indicators, as shown in the following figure.

Change the value format of Growth Compared to 2013 to percentage. For details, see Value Formats in Tables.

Take the year 2014 as an example. Based on the formula (341.17 / 290.53 - 1) * 100%, we can get the result 17.43%, which equals to the data in the table.

For the demonstration of cross-row calculation, see section "Preview".