So far we have looked at comparison analysis, trend analysis, key metrics analysis and ranking analysis. The last form of analysis I would like to look at is contribution analysis.
Contribution analysis is about understanding what the percentage contribution of each item is and how it contributes to the total. The reality is that the human brain is not great at understanding large numbers, so it's much easier to understand percentages.
Percentages are always done in the context of a the total being 100%, so it's very quick to see whether a percentage is a smaller amount like 10% or a large amount like 70% regardless of what the actual numbers are that are used to get the percentage.
The pie graph is probably the misused and abused visualisation and I generally steer clear of using it, however one place that it can be used appropriately is for contribution analysis and to display the proportion in relation to the whole. But whenever I teach on this, I always give a few rules to follow when creating pie graphs. These are:
The next types of analysis I'm going to focus on are Key Metrics and Ranking Analysis.
Key metrics displays one key piece of information prominently (examples include total sales, total profit or total number of customers) and by using ratios you can get deeper insight and more information from your key metrics. For example we could create key metrics for displaying the profit ratio (which is the total profit divided by total sales) where the profit ratio is displayed as a percentage, this makes it easier to see what portion of your sales is profit.
Another ratio that can be calculated is the average sale per customer or the average profit per customer. In this case you would divide the total sales by the number of customers and the total profit by the number of customers, so by using three key metrics you have now created three new key metrics using ratios which gives further insight into your data.
Ranking analysis is about understanding the top/bottom values within a range. The simplest form of ranking is to do a sort from highest to lowest or lowest to highest but there are many more rankings that can be done.
Top 10 or bottom 10 is one of the common method of analysis to highlight the items that are contributing the highest or lowest within your data set. Filtering your items to only show the top 5, 10 or 20 makes it easier for you or your audience to view the most important items in a table, rather than displaying a long list of items. A top 10 or bottom 10 analysis can also be combined with your column or bar graphs to restrict the number of items that are displayed in the graph.
You can also use a top/bottom percentage filter, in other words limiting the list of items to the top 20% or bottom 20% and this again easily allows your audience to focus in on the key values within the data set.
Regardless of what job you do, you will regularly make decisions, and in order to make these decisions, you need data. Data surrounds us and, whether we know it or not, data informs our decisions so being able to analyse data means being able to make better decisions.
As technology has progressed, it's become easier to put systems in place to handle data, but it's also increased the amount of data we have available to us so we're constantly looking for easier ways to see the story behind the data. The introduction of computerised spreadsheets was a big moment in data analysis because suddenly anyone could input information, run a formula or create a graph and find an answer and as these tools have progressed, the access to information has exploded!
As a trainer and consultant, I've chosen to focus on data analysis and using Excel and Power BI in order to analyse data. I spend a lot of time teaching people how to use these tools but I also spend time teaching different forms of data analysis because what a lot of people don't realise is that knowing WHAT questions to ask is as important, if not more important, than being able to put together an impressive dashboard.
The two most basic forms of data analysis are comparison analysis and trend analysis so I'm going to start with them.
A comparisons shows you things like what items sells the most or which product is not doing well. You can compare different months or regions to see where the biggest profit or lowest turnover is, you can also see how much of a difference there is between different products or time periods. Tables are the simplest way to do a comparison analysis, or if you'd like to visualise it then a column or bar graph is best. A pie chart is useful if you're only comparing two or three items but should be avoided for more than this.
The column or bar provide a good indication of how large or small an item is in proportion to the other items that are being displayed. If there are a large number of items in the graph then a bar graph is a better option as the labels are displayed horizontally and are easy to read.
Trend analysis is about understanding how data changes over time. Trends can be understood by many different time dimensions such as year, quarter, month, day of the month, day of the week, week number, hour and second.
Understanding your data over different time periods will provide important insight into whether things are moving up, down, staying stable or is volatile. The analysis of different time periods allows you ask a variety of important questions such as:
When working with trends you should use a line graph or area graph. The line graph will provide a good understanding of the general trend over time. A trend line is often added to the line graph and provides a good visual indicator of the trend.
An area graph is a good option for when you want to include a comparison and see how it is trending. For example an item could start off small, but grow over time, an area graph is a good method to display this information.