|
One of the most common challenges that faces Excel users is having two separate sets of data that need to be referenced for reporting purposes. In this video I show you how to use Power BI to create a relationship between two spreadsheets in order to create a report using information from both. I've used a common challenge which many HR professionals encounter, where there are two sets of data, an employee training file (which records all training done by every employee) and an employee master file (which has details for each employee such as department, gender, marital status etc). I show you how to create quick reports and graphs to easily visualise information such as total training cost by department or percentage training costs by gender. In Excel, these reports traditionally take a long time because they require complicated formulas and VLookUps but in Power BI it's done in a few simple clicks.
For more information on our training, please look at our classroom training courses.
0 Comments
Our previous post "Data or People? What is the most important resource in a business?" discussed the importance of developing your staff data analysis capabilities. Here we will outline the key steps in the journey to being able to use the latest technologies and techniques to produce reports and analysis. The following is focused on users who primarily work with spreadsheets and use Excel. Step 1 - Learn to use Pivot Tables Pivot Tables will speed up the time taken to produce reports and analyse your data tremendously. An important aspect of Pivot Tables is that you need to understand how to structure and format your data for Pivot Tables to work effectively. Step 2 - Create a Data Model New technologies such as Power Pivot and Power BI allow Excel users to easily create powerful data models. Data models allow you to easily centralise the storage of your data, calculations (business logic) and create relationships between the tables of data. Data models make it much quicker and easier to develop reports and speeds up your processes. Step 3 - Learn some Analysis Techniques Once you have an engine for the production of your reports, you need to learn how to ask questions of your data. There are many different types of analysis that can be performed on your data - comparison, trend, ranking, contribution, variance, frequency etc. are some examples. Add a bit of simple statistics and you will find a whole new world of interpreting and understanding your data. Visual analysis is a growing field of importance. Learn which graphs to use with which type of data. Visual analysis provides critical understanding of patterns and relationships within data. Step 4 - Time for New Data Technology such as Power Query makes it easy to access new data and to mash them up with existing tables of data. However the data may not always arrive in a format that is easy to use for data analysis. This is where you need to know how to use different methods to transform, append and merge data that creates powerful new data sets for different insights. Step 5 - Share your Insights Data analysis is of no use if we are not able to share the new insight and knowledge. Learning how to create effective reports and dashboards that can easily communicate key insights and information to your audience. Being able to tell a story about what your data means is a skill that will develop over time as your experience develops. Unfortunately often trial and error is required to find the best methods of what resonates with your audience and what does not. My advice is start simple with your presentations. Your audience will quickly tell you what they want. The ability to manage and use data in an effective and efficient manner has become critical to the continued viability and success of most businesses. Data is the key resource to informing decision making. It can be argued that data has become as important as cash when it comes to running a sustainable and profitable business in today's complex and uncertain business environment.
So why is it that so few businesses have a strategy and plan for the training and skills development of their knowledge workers to develop data expertise and capability? Many of today's knowledge workers spend countless hours working with data using obsolete technologies and techniques. I am continually amazed to see how many businesses are still running old style Excel spreadsheets with quadrillions of formulas in a multitude of sheets that nobody understands. A lot of business are running a lot of its processes through spreadsheets, and ;ets not kid ourselves, this a potentially very dangerous situation! Its probably fair to say that a lot of businesses are basically running on Microsoft Excel. So my question to them is how many staff members have been properly trained to use the full Excel suite, including Pivot Tables, Power Query, Power Pivot and Microsoft Power BI? We can even go a step backwards and ask how many businesses have trained their staff on the basics of data and visual analysis? My own anecdotal evidence from having trained thousands of people in the past 15 years is that not many people have received proper training in these technologies and techniques. I normally find that people have a basic understanding of Pivot Tables, which they either learnt from the person sitting next to them or as part of a course they did which just touched on them, and have little or no knowledge of Power Query, Power Pivot and Power BI. The reality is that data is useless unless it's transformed into information. I often see CEOs complaining that their companies are data rich, but information poor. So what should businesses do? I would recommend that the people who are knowledge workers and regularly work with data should be identified and then trained in this field to improve their skills and capabilities. We have proven that by simply changing the way you use Excel, you can become 80% more productive in 20% of the time. People who are trained to analyze data and that can ask meaningful questions of data are invaluable to any business who wants to make good business decisions that are driven by all the data they already have. These people are able to:
So in summary, data is useless without the people who have the right skills to understand how to work with it and businesses should therefore focus first on ensuring their people have the right skills and training to work with data before attempting to make data driven decisions. 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 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 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. Comparison Analysis 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 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.  |
