What is profit ratio?

Profit ratio, or profit margin, is the division between the profit and revenue of a company. It shows the profit a company makes per dollar of revenue. For example, if a company has a profit ratio of 0.1, or 10%, it means that for every 1 dollar of sales, it earns 10 cents of profit. The profit ratio is calculated as shown in the expression below.

Why Is Profit Ratio Important?

Usually, the profit ratio is used as a proxy for the efficiency of a company's operations. It is an indicator of the viability and potential of a business. As a data analyst, it is important to calculate and visualise profit margins to provide decision-makers with a clear picture of a company's financial health.

How to Calculate Profit Ratio In Tableau?

You might be thinking: the calculation of this metric is pretty straightforward since it consists of the division of profit by sales, and that can be easily achieved by a calculated field in Tableau

Yes, you are right. However, the problem arises when we are not interested in the profit ratio of a single record in our data, but rather in the aggregated profit ratio. Tableau aggregates data automatically in many situations. Suppose we create the calculated field as shown below.

Profit Ratio = [Profit]/[Sales]

Since there is no aggregation function, Tableau will calculate the profit ratio for every row in your data. If you go ahead and use this field in a visualization where Tableau aggregates it with a sum, it will actually execute your calculated field (division) and then sum the results. However, what we want is to first sum all profits and revenues and then divide them.

To explicitly tell Tableau to first sum and then divide, we need to change our calculated field to include the sum function:

Profit Ratio = SUM([Profit])/SUM([Sales])

The image below illustrates why the two procedures result in different aggregated ratios.

Conclusion

In Tableau, it is essential to be aware of the sequence of operations that your data undergoes and the possible automatic aggregations that occur under the hood to produce a specific visualisation. To calculate an aggregated profit ratio, it is important to first sum profits, sum sales, and then divide them. I hope this helps, and enjoy analysing data!