In this post we are going to look at using a Dual Axis Bar in Bar Chart to compare Percent of Sales against Percent of Profit by Sub- Category. This is a handy way of looking at sales relative to profit to see which sub-categories/product are performing in both measures.
How to build a Dual Axis Bar in Bar Chart to compare Percent of Sales against Percent of Profit
When we take a look at how an initial chart might look when pulling in the relevant data, we can see that there is already quite a bit of information We have overall sales and profit figures in monetary terms but the relationships between the two in terms of how those pies are divvied up is difficult to discern.
Before we go any further I want to get rid of any sub-categories that were not profitable thereby removing any loss from the overall total of money made on each sub-category.
We do this with a quick filter. This does not effect the monetary totals but does effect, in fact skews, the distribution of percentages.
Next thing we want to do is go to those percentages. Go up to Analysis above the chart, left click and choose Percentage Of > Table.
You can see that this has changed the Axis to % of Total Sales and % of Total Profit.
You now want to change the colour by dropping SUM(Sales) onto Colour on the SUM(Sales) Marks Card.
You will need to do the same for SUM(Profit) onto the SUM(Profit) Marks Card and from there you can Edit Colour.
So far so good. Next we want to ‘merge’ the two by creating a Dual Axis. Go to the SUM(Profit) pill in the Rows Shelf and from the dropdown select Dual Axis.
You can see that the bars have now merged into each other but they are to hard to see and it is impossible to distinguish between Sales and Profit. To remedy this, simply go back to your Marks Card and change the Size of your bar in SUM(Profit).
We now need to make sure that the Axis of both measures is synchronised. Right click on the % Total of Sales and select Synchronise Axis.
Just to make it look better, and to give the viewer a better sense of what they are seeing, you can order the measures and change the headers in the Axis to match the colour of the bars.
About The Information Lab
The Information Lab Ireland is at the forefront of creating a data-driven culture in Ireland.
As part of its vision, The Information Lab Ireland regularly hosts free events throughout the country to show how being data-driven can improve decision making and lead to a better understanding of the world around us. For more on these events please like us on Facebook, follow us on Twitter or catch up with us on LinkedIn
