Data visualizations: Think about what you’re asking for.
You want reports, you want dashboards, but what you really want is data that you can use to make informed decisions! The problem is it is difficult to articulate what kind of data visual would best inform your needs–so you often end up with a big report or an Excel spreadsheet full of columns and rows. There must be a better way!
In this ebook, we are going to look at data visualization and understand why data visualization is important.
Specifically, we’ll take a look at
- how the human brain and the eye interpret data,
- the importance of that data on the data model,
- the importance of making sure you choose the right visualization type and
- learning how to tell a story with your data.
I started working with ERP solutions, implementing accounting solutions with various different products, including many of those products from Microsoft. And what I’ve noticed over the years as you spend a lot of time entering data, entering production orders, sales orders, purchase orders, journal entries, all this information into your ERP system. And unfortunately, it seems like it all gets locked up. It gets put into a bunch of different tables. And while these ERP systems may give you different reports, they don’t necessarily give you the true view of the data that you’re looking for to really make decisions for your business.
Many systems understand this problem and so they’ve started adding other solutions to their product lines that allow for dashboards and some built-in embedded analytics inside the solution. However, if you’re not careful, the next thing you know is you’ve got charts and graphs and KPIs coming out on every screen.
Unfortunately, you don’t necessarily control where that data comes from or know what calculations are behind it. The next thing you know you’re looking at a screen and you just are overwhelmed. So you’ve gone from one extreme, not having all the data to maybe having too much data just thrown together on one page.
The key to any successful report is data. When thinking about what you need from a BI solution and a solution like Power BI, good data underpins the whole solution.
Data is Key
Data is like an iceberg, with beautiful, eye-catching sculptures on top, and a huge mass of data hidden below, out of sight behind the charts and visualizations.
With Power BI and data visualization we’re going to consider, the size of the charts, what colors to use, how to align that information.
Your data, of course, needs to be built correctly underneath the water, you need to have the right measures, the right relationships, be pulling in all the data sources, and have everything modelled. The data needs to be clean and working. That’s 80% or 90% of the entire picture.
So the first thing you need to really think about as you’re planning your data visualizations is, is my data correct. Without the data being correct, you’re not going to have successful reporting.
Data modelling can all be done inside of Power BI or you can create external models with external sources like SQL Server Analysis Services.
Generally speaking, Power BI works best with a dimensional model. Even though a lot of Power BI reports are built on strictly one table, often multiple facts and dimension tables need to be combined.
When you’re thinking about what you need on a report you need to consider:
- Where does this data come from?
- Which tables include the information I need on my report?
- How do those tables relate together?
- What are the measures?
- What are the numbers I’m going to show on the report?
The data, information and relationships can all be built-in power query or by using power pivot in Excel. Once all the data is available, then we start moving into that top 20% or visualizing the data.
Why is data visualization so important?
The data visualization process begins with the data that was locked up in the ERP system and turning it into information that we can make sense of. From there, we need to visualize the data so people can digest it and understand what’s going on. We’re going to take them down a path, tell a story, consider yourself kind of the author of everything you’re putting together so that they can learn and take action.
The process is drawn out as stairs, however, it’s really a circle or a loop because once action is taken, it will create more data that we can then take this journey on again.
Now what you can hopefully see is that there is quite a jump from information to learning. So if the data visualization and storytelling is not done correctly, then the learning phase can not be achieved.
A lot of research into data effectiveness has been undertaken over time. There are a lot of experts in the area and some information on useful books are included at the end of this ebook.
“Fundamentally, Data Visualization is not about technology, but about Questions and Answers” – Jeff Heer
“If no one remembers the numbers then you have not communicated” – Robert Kosara
How many times have you walked away from a business meeting or a board meeting thinking, I remember that division was large, but I don’t know what the numbers were.
Unfortunately in a lot of cases that is our problem. We might not understand the data. So since we don’t understand it, it’s really hard for us to explain it well.
Can you remember more than a Goldfish?
Microsoft undertook a study a few years ago and found out that the goldfish has an average attention span of nine seconds.
In 2000 they also found out that humans had about a twelve-second attention span, however in 2012 that had reduced to an eight-second attention span.
So goldfish actually was able to pay more attention than we humans are able to do.
They also found out that 7% of people forget their own birthday and 25% of teenagers forget major details for close friends.
Why is all this happening? Why can we not pay attention anymore? Why do we not remember important facts, important things about our friends and our relatives?
It really has to do with the amount of data that we’re receiving on a daily basis, whether that be from our phones, emails or from social networking sites.
Multitasking is really causing us problems and has a serious impact when trying to pay attention. We’ve all been in meetings where people are spending time looking at their phones instead of actually paying attention to the meeting.
So the more stress we’re under, the more information our brain is receiving, the less capacity we have to interpret this new information.
So this is really something analysts are battling with when trying to deliver data via information and storytelling so that our executives and those using our data can truly understand it.
So how do we tend to solve it? Often by using a spreadsheet – everybody’s addicted to a spreadsheet.
However, Excel syndrome can evolve – after creating your first spreadsheet, another problem arises that needs analysis, so the spreadsheet gets bigger, then a new problem arises and the cycle continues.
To test the goldfish effect and the usefulness of a large spreadsheet, set yourself an 8-second challenge.
From the spreadsheet below, can you find…
- which product category has the largest revenue and
- which country has the largest revenue?
Set a timer for 8 seconds & have a go…
Are you able to find the answers in 8 seconds?
Well, what you’ll see here is that it was the computer product category that has the largest numbers and United States that had the largest total.
Let’s try that again with different charts. This time I want three answers.
- Which product category has the largest revenue
- Which channel has the largest revenue and
- Which promotion has the largest revenue
Set a timer for 8 seconds again and have another go…
Is it a little easier when you’re looking at it this way? The answers are computers, the store, and the North American back to school promotion.
Now, these weren’t really good visualizations because I could have easily sorted them, which would have made it jump right out at you.
So why does this happen?
How the human eye actually works
As we go through the spreadsheet information as an English speaker, we look left to right, top to bottom. We will typically look at each number, put that information into short term memory, compare them, save the results into my long term memory and move on to the next one.
When we look at a picture, we don’t go in order. We go to the things that actually jump out to us.
In the screenshot above, you probably notice the green bar first, the largest one out there and then jump down to acknowledge red as the smallest bar.
We don’t necessarily go left to right top to bottom – we see what stands out first and only see a few things at once. So you don’t actually take in necessarily the whole chart. You seek meaning and make connections.
The largest bar probably means largest sales. That connection is automatically made, partly by relying on existing conventions and metaphors. Green is often good, red is generally bad. So these are things you have to keep in mind as you’re moving through and creating visualizations for end users.
How do we create visualizations that people will really use?
So now that we kind of understand why we need visualizations and how the eye works, how do we create visualizations that people are really going want to use. Here are some key points.
People are looking for immediate answers to their questions – so well organised, focussed summaries are a great start.
Try not to design a dashboard for everyone, keep them focussed on a specific task or function.
Remember the reports may be accessed from a mobile device, so they need to be clear and easy to read.
Using the right visualization
Power BI provides many visualizations out of the box, for example, the stacked bar chart, the stack column chart, line chart, arc chart, waterfall chart, pie chart, gauge chart, maps, slicers, tables, matrix, the list goes on and these are just out of the box visualizations.
A marketplace also exists where you can download and include many different visualization types. So having different visualisations is not a problem in Power BI – the problem really surrounds choosing the right one to use.
Often a client will come to me with something like this and say, “Hey, I need this report available in Power BI”.
Guess what I create? They’re paying my bills. I created a table or a matrix report that shows them their sales revenue. Now what I might also do is then maybe create an individual visualization. So here’s the same information produced in a line chart.
With the information presented this way, it’s easier to see that Asia passed Europe in May and also the significant increases in North American sales in April. Both of these items could have been missed by only having the information in a table.
As already mentioned there are many different types of available visualizations & choosing the right one can make a massive difference in how the data is understood, so it’s important to choose your objects with care.
You need to be careful with a pie chart, in the example above it’s not particularly easy to see which are the largest companies. Fabrikam and Contoso are definitely larger than the rest, but 3rd and 4th places could be Proseware or Adventure Works, it really isn’t clear. Removing the middle of the pie to create a donut chart doesn’t really help.
Really when we have this many categories, neither really solve the problem. Adding labels with numbers requires users to read them and the visualization loses its impact and may as well be presented as a grid.
The same information presented as a column chart is much more impactful as you can quickly see which company is the largest.
Now of course the best approach is to actually sort that so it is absolutely clear what order they go in, Fabrikam, Contoso, Adventure Works, Proseware.
Now I have a lot of clients that go but that’s boring. Everybody uses column and bar charts. Well, there’s a reason why everybody uses column and bar charts because everybody understands column and bar charts. So again, remember your audience, let’s get that information out to them very quickly, very easily.
You may want to do some things with colors to make this look more elaborate, but do I really need to? This quickly gives me my sales by manufacturer.
In summary for this section, choose your objects with care. Just like your doctor probably tells you. Be careful around pie and donuts!
Consider the Scale
Also, be careful about how you use those charts. On this first chart, you may think that Switzerland is really struggling.
They’re way behind Poland, Sweden and the Netherlands. It’s a huge gap. But if you take a closer look at that chart you will notice the axis does not start at $0 million. So it’s actually not as much difference as you might think. Changing the axis to start at $0 makes a big difference. They’re all within just a few thousand dollars of each other.
The use of the axis scale can be very misleading. But again, that may be the story I’m trying to tell. Maybe $14.5 million is our minimum quota and Switzerland just barely made it. That might be the message I’m trying to tell. So keep that in mind as you’re creating these charts and these axes.
Also, remember line charts are best used for showing trends. Using a line chart for the same country data we just looked at, at first glance, could be interpreted as sales dipping.
However, by resorting to the countries, it looks like sales are going up.
So line charts are really designed to tell a story across time.
Choosing the right chart
How do you choose the right chart from so many different options? I really like Abela’s chart type hierarchy. It’s not designed specifically for Power BI or any visualization tool, but can be used in any circumstance.
You start in the middle and work outwards based on what you want to show.
Keep it organised
Now that we understand how to choose the visualization, we’re going to move into keeping those visualizations organized. So the first thing when you’re creating visualizations is remembering contrast gives focus.
If you want to draw somebody’s eyes to a particular number, bold will work very well. In this snapshot above, year over year growth really jumped out because it was bold.
Colour is hard to master
Black on white text works well. White on black text works well. Yellow on white text, very hard for us to read. Blue on black text is not working very well for me either. So you need to be very careful. You also need to remember that you may have users that are colourblind, so you need to keep that in mind.
Also, remember color can be distracting.
As a general rule, don’t use color in charts just to make it look pretty. That blue and orange text and shading doesn’t mean anything. So it’s truly distracting away from the numbers.
This is just a sample of some color palettes from Steven Few’s books to which I’ve added the hex codes.
I’m going to show you in a minute how you can use those hex codes to actually create a theme inside of Power BI.
Demonstration – Colours and Themes
Let’s move into a demonstration and talk about how we can select colors and themes.
This report in Power BI desktop already uses specific colors. Different themes can be chosen from within the ribbon bar in the “Switch Theme” menu.
To change the color palette for this set of reports, choose a different theme and the color scheme will change.
Now one thing you’ll notice here is it also talks about theme galleries as well as importing a theme. Several different themes are available for download from the Power BI community site or you can actually use your own theme JSON file.
The bottom option from this list provides instructions on how to create a theme. But there are other tools available that can also help. I use the theme generator on the PowerBI.tips website.
In this theme generator, you can actually create your own themes and set the global colors that you want to work with. You can drag the pinwheel to select a color or enter the hex number if you know it. I’m going to select four colors for this theme.
Now the thing I like about this theme generator is it’s more than just colors. You can amend different features in the chart options as well.
Click on the down arrow next to the chart you want to amend to review the different options and make changes to that chart as required.
In the example above I’ve set the text size on the legend to always be 12, the text size on the category axis to be 14 and always be bold. You can set default settings for many of the different visualization types. When you are happy with your theme settings, give your theme a name and download it as a JSON file.
So it’s going to become my session theme and once that is downloaded, I can easily jump right back into Power BI desktop and import that theme.
The thing to keep in mind with themes is every Power BI desktop file starts with the default theme. If you have created a corporate theme, remember to import that before you start working with your reports.
Backgrounds can help you organize the layout of your visualizations. So this is an example of a background that can be used on a report. You’ll notice all of the different boxes available.
I actually use PowerPoint to create my backgrounds so I can initially start inside of Power BI desktop, determine how many areas, how many visualizations I’m going to use, and then I can create those shapes and lines to group those together inside of PowerPoint and bring that as a page background into Power BI.
Demo: Creating a background
Let’s go back to Power BI desktop and review my report format. I have four smaller boxes across the top containing three key financial stats and a small donut chart. Then underneath I have a larger bar chart going across the full width of the report.
I find this a little easier to line everything up rather than just using borders with the different visualizations. I created this in PowerPoint by creating a New Slide with no initial formatting and inserting different shapes.
So maybe I want to outline my entire report page so I can go ahead and draw that large box with rounded edges and expand to fill the entire page.
I then removed the fill color and increased the weight of the border. I added a similar smaller box and then copied it twice to have three identical small boxes across the top.
One of the nice features of PowerPoint is I could easily line those up using the align options to distribute them horizontally across the page.
I amended the fill and weight of the outline to style them how I need. Then I selected all the shapes using the right mouse clicked and group them together and finally save that as a picture.
Back in Power BI desktop create a new page and all I have to do to use that background is go to the format painter and select the picture file I just saved into “+ Add image”
Select “Fit” in the Image fit option and set the transparency to zero so I can then see those boxes.
So now that I have those boxes, I could easily take visualizations and start dropping them into the spaces I made in the background.
Tell A Story
Telling a story is where we’re really putting together, for lack of better words, a book to navigate our users through their experience with the data and taking that data into information. So how do we do this?
Build Easy navigation
We can actually do this by building some very easy navigation into our reports by using
- buttons to create menus or navigation
- drill throughs to provide access to additional detail
- tool tips to give us some of that detail right at a glance without having to drill down and
- bookmarks that tie all this information together and which are especially useful when used with buttons to hold that navigation together.
The easiest way to do this is to look at an example. We’ll start at the end to see the finished, and then walk back through how it was all built.
Example: 2019 Baseball Season Stats
So to start off, I’m going to jump out into Power BI.com the Power BI service.
This is a report book detailing the 2019 baseball season which has really been designed as a story. Initially there is just one page visible for this report book, the home page, but across the top some navigation is available to show the different sets of data, which in this example are:
- League Standings
- Top 10
- Team Hitting
- Against Opponent
By selecting the league standings option, I can quickly jump to the league standings page.
I’ve also included drill-downs and tooltips. So if you hover over the Yankees, a little tooltip window pops up and shows more information about their wins and losses.
The same window also gives a user instruction and tells me to right mouse click to drill down further into this set of data and access the additional game detail.
So when I do that it’s just going to jump me over to another page, show me all the game detail where they played, whether they won or lost.
A back button is also provided to allow the user to quickly move right back to the beginning or back up to the top level of data you’re started at.
Another navigation option offer details about the Top 10, including home runs, hits, doubles, with dropdowns available along the top to filter the information.
On this page, the tooltip is used to display more information about a particular player. Hovering over the player’s bar in each section will display a custom tooltip, in this example including his picture.
In a similar way to the previous set of data, I could also right mouse click and drill down into the data and get more detail about this player as well.
So whilst there is a lot of information behind the scenes it is all very accessible using buttons, bookmarks, tooltips and drill throughs to navigate through the data to see the story.
So how did I do all of this? Let’s go ahead and jump back into Power BI desktop.
So first of all, you’ll notice there are actually several hidden tabs along the bottom in this report book. They are hidden by a simple right mouse click and hide. So I design out the different reports that I want and then I decide the pages that I need to work with.
The menus choices are simply buttons.
So if I select one of the buttons, you’ll see on the right-hand side menu, I can put in my button text, select the font color, padding, text alignment and size.
Buttons can also have different appearances depending on their state. So you may want the button to change color when you hover over it or press it for example. Select “On hover” or “On press” and amend the button styling as required.
So there’s a lot you can do cosmetically with those buttons, but everything is really driven from the choice here called actions and those actions.
I can go ahead and turn an action on this button and that action is a bookmark.
Now that bookmark is a snapshot of a page, that you can create from within that page.
So if I go ahead and go to the league standings page and turn on my bookmarks pane from within the View menu at the top
What you’ll see on the right-hand menu is the list of bookmarks ( Snapshots of pages) and here you can see I have taken a snapshot of this page called “league standings”.
To add another bookmark, I can just go ahead and simply say add, then right mouse click and rename it. In this case, I’m just going to call the new bookmark “League Standings 2”.
One thing you have to keep in mind is whether you want to snapshot the data. That’s very critical when it comes to having the slicers/data dropdown filters on a page. Because if you do snapshot the data, it’s going to snapshot the slicers and where they are.
So in this example if I look at my top 10 Bookmark, where I have everything set to ensure iot shows all.
I need to make sure that data is not selected.
This approach with Bookmarks makes it very easy to set different page states, and simply set the action of a navigation button to take you to the relevant page in the relevant state, by selecting the bookmark you want to go to. The bookmark becomes the destination when the button is clicked.
NB: To simulate clicking the button and moving to the next page when you’re in Power BI desktop, you need to make sure you hold the control key down, allowing you to test the navigation as it will work when the dashboard is being used.
Creating Tool Tips
I created tooltips on the League Standing page so that when I hovered over a team, the information related to that team was then displayed. When you hover over different teams the tooltip changes based on the context of what I’m hovering over.
To create this, first, you need to turn on the Tooltip option which is within the format painter option of the menu on the right-hand side.
Then select between default or report page. In this case, I used the report page and selected the instructions page and chose a label color, value color, font and text size.
So if I look down here on my list of pages at the bottom of the window, you’ll notice I have a page here called Instructions.
Now this page is a little different than typical pages.
If I go to the Format painter on the right-hand menu of this page and look at Page information you’ll see I have this set as a tooltip, so I’m telling Power BI that this page we’re looking at can be used as a tooltip.
Then set paper size, in this case, I have used a custom size. There is also a standard tooltip size which is a little smaller, but I prefer to use custom and make them a little bigger.
In this example, the left-hand pane shows the data, and even though the visualization includes details for every team when this is used as a tooltip, it’s going to actually filter or send that information across to this page, so I’ll only see the team I’m hovering against.
The right-hand side of the tooltip contains some basic static instructions input as text that will be displayed regardless of which team I’m hovering over. So this is just text that won’t change.
This tooltip is tied to this visualization by simply turning the tooltip on and then pointing the report page to the instructions page where the detail is saved.
It is also possible to use Automatic tooltips however this tends to simply display data from the fields you’ve assigned on those tooltips.
So in the example of my Top 10 page and my player tooltip. The Tooltip is set to Auto.
You’ll see here on my Player tooltip page that I have all the player information.
The Full Name field in this data is tied to this Players Tooltip page via this Values setting on the right-hand Visualization menu of this page.
So whenever we look at a part of a report containing the player Full Name field, when I hover over their name the player detail tooltip will be displayed.
So I could create a new visualization, select the data I want to include (I chose players full names and injuries as below).
Then go to the format painter and turn on tooltips for this visualization. Set the Type as “Report Page” and page as “Auto” because the tooltip is already tied to the Full Name data field.
This means that as soon as I hover over the data containing the Full Name, the tooltip is displayed.
This is just a quick example of how tooltips, drill throughs and buttons can be used as part of telling the story.
Creating useful, interesting and engaging data visualizations is easier if you always remember to follow a few key points.
- always be consistent.
- have a plan of the information to be shared and how the story will be revealed.
- sketch it out or layout the page to work out how many visualizations across how many columns are needed.
- get someone else to look it over, make sure they understand it and can follow the story. It’s often useful to ask someone with no prior knowledge to help as they could pick out elements that are not clear. If they understand it then you know you are really telling the message correctly.
- understand the audience, who and how they’re going to be using it.
- design is important, data design is key however…
- the visualizations are actually telling the story
Always remember: as the author, you’re the one that needs to tell that story.
Resources and References
Here are some useful resources and references you may also like to look into. More information about the baseball report book is available on my blog including how to create small multiples and conditional formatting tips.
Also, I wanted to recommend a few books that will help you:
- Stephen Few’s book: Show Me the numbers. It’s not written specifically for Power BI but just written for anybody who does visualizations and could really help you in designing tables and graphs. – https://amzn.to/2N3UuvO
- Storytelling with Data: A Data Visualization Guide for Business Professionals by Cole Nussbaumer Knaflic – https://amzn.to/2T2Xdt5
- Good Charts: The HBR Guide to Making Smarter, More Persuasive Data Visualizations by Scott Berinato – https://amzn.to/39Uj5wX