PowerBI is a great tool for data visualization and (some) data transformation, no doubt about it. Over the last years of its development, it gained many great features and capabilities. If you don’t know it yet – be ashamed! I recommend keeping an eye on the Microsoft PowerBI blog to be up-to-date with new features and releases. There are also many resources available on the Internet if you’re looking for training materials (which is not this article). I assume you touched this technology at least a bit but at the same time, you’re not a hardcore analyst as you probably know all these tips already.
This article will not tell you how to do all the things you possibly could with PowerBI, in fact, you should try it yourself, it’s very intuitive and allows to build very advanced visualizations. And once you stumble across a challenge you should look up online how to approach it. That’s exactly what we did here in Predica – we built a companywide analytical reporting that anyone in the company can use without extensive training. Not to be modest, I would even say it took us very little time to achieve it, but we have already invested much effort into building and maintaining proper data sources. Therefore, I’d like to share some experiences we had and little hints we use in creating the reports both for us and for our customers.
Tip #1: Simplicity – don’t go too fancy about visualizations
Following the idea of delivering a message… There is an increasing number of visualizations available in PowerBI which you can get from the gallery Some of them are pretty complex and can show you relations between data elements in a unordinary way, that can make sense… quite rarely (for example if you’re a hardcore analyst).
For most of us ‘ordinary people’ – and I’m saying probably 98% – simple means better, easier, clearer, …..er [put here whatever you think suits]. So focus on simplicity! In most cases, a (boring) bar or line chart will surely suffice. Also, don’t fear the old-school and ‘ugly’ tables – they are still the best way to present raw data, which is sometimes all you really need (and why you keep using Excel for!). For example, I try to avoid pie charts and treemaps for a very simple reason – you cannot see the difference between pies which have similar value.
Let’s try to report the sales volume per region – try saying if red or orange is bigger or how much they differ:
The general rule-of-thumbs about visualizations:
- Remember that people read from top left to bottom right, so put most relevant stuff (KPIs?) where users go first
- Vertical bars – for general data display – avoid rankings, use sorted data, it’s easier to read it
- Horizontal bars – these are actually best for data rankings
- Line – usually for time series when you need to compare multiple series of data, for single bars works just as good
- Bar/line mixed – to present two values of different types (like money and percentage)
- Bubble – to present 3 different number values (two axis and bubble size)
Tip #2: Context – interrelations between elements
One of the coolest features of PowerBI is the cross-filtering capabilities. It means that once you have two charts with connected data next to each other when you click on an element on one, the other will be filtered based on what you clicked. This greatly helps with the data comparison, kind-of-visual drill downs, and simple analysis.
But what might not be so obvious at the first sight, you can actually use three ways of filtering and connecting data which can make your analysis experience better and easier. Let’s consider the project management example, where you’re interested in seeing the time reported by people (top bar in the below example) and the time reported each month (the bottom bar), where you can see the different behavior the interactions provide:
- None – no filtering happens between elements – use if you want to display data as it is so that it’s not affected by users’ behavior. In the example – clicking on the bar in the top chart does not influence data displayed on the bottom:
- Highlight – the filtered value is displayed in the context of the total – use when you want to show how much of the total is the selected element. In the example – clicking on the bar in the top chart fades out the bottom chart leaving highlighted only the part of the bar which is applicable to the clicked element:
- Filter – the actually filtered value is displayed – use when you want to see what actually hides under the selected element, meaning you are interested in the detailed data and not its relation to the total. In the example – clicking on a bar in the top chart filters out the bottom one and leaves only the data applicable to the clicked element:
So depending on the context in which you are viewing your data, it may have a significant difference what relationship behavior you will select. Especially, when there’s a lot of data elements it might greatly influence the ease of use of the report especially for not advanced users (which we usually create such tools for).
Click here to find more info about creation of interaction between visualizations
Tip #3: Divide and conquer (or slicing and dicing) – filters
The most basic concept of the data visualization, yet you might still be surprised by how many filtering possibilist are there in PowerBI reports – 5 obvious ones are there.
Basic report filters panel:
a) Visual level filter – filter data only at the selected visual level, which can be particularly useful if you want to have some background (not visible in the chart) data used only for filtering
b) Page level filters – apply to all elements in the page
c) Report level filters – apply to all pages, which can be particularly useful when user is supposed to journey through the pages to see the data in the same filtering context but with a different view presented in each page – once you select the filter and move to the next page, the filter stays selected which allows you to see the data in the same context:
And two in-canvas filters:
d) Slicers (in canvas filters) – filters available as single or multiple selection checkboxes or dropdowns, I found them not particularly useful, as they take the canvas space and considering cross-filtering capabilities of most visualizations, do not provide much value added. Also, like the page level filters work only on a particular page which in the majority of cases I worked with is rather limiting, as when you go to the different page you lose the context of the data you worked with.
e) Cross-filtering (as described in previous point) – the additional idea behind these filters is that they can be used instead of (somewhat dull…) slicers to include additional information (selected measure) if instead of for example checkboxes list you create a vertical chart you can use it just for filtering – just click the bar to filter out everything else:
Again, considering the project management example you can think of having multiple page report with pages giving you an overview of hours (like in the interactions example) or details of time reported under particular tasks (as the above example). So, if you use in-canvas filters, you need to select the project you are interested in each page individually, whereas when you use report level filters the project is still selected when you browse through different pages. Now imagine having a report with 7 or more pages… try it yourself and you will see how much sense it makes.
Tip #4: High or low perspective – hierarchies
Hierarchies are a great way of showing data analytics on various levels of granularity using the same visualizations. For example, in a project management domain, a program manager may be interested in project(s) progress and time reported in months period, whereas a project manager could be interested in a weekly level to look into what is happening more closely. Obviously, you can create different reports for each of them, but then you will end up managing and supporting a large number of such cases. Alternatively, you can be clever and design a report in a way which can be used by both. And this is where hierarchies come in handy.
There are three ways to use hierarchies:
- They can come from the data source (typically OLAP/Tabular alike) so basically present in the data model
- They can simply be based on date and time data – here PowerBI does a nice thing for us and allows to present any time data as a Year/Quarter/Month/Day hierachy (more here)
- Or you can put more than one dimension in the visualization, which doesn’t make them visible but allows to drill from one to the other
Once you have any, just notice the small arrows that appeared in the corner of the chart which you can use to go up and down the hierarchy levels:
The same visualization and report is used to achieve different perspective view. Since it’s easy and fast to create reports in PowerBI, you can be tempted to create many of them just because you can. But think of the poor users who will be using these reports and how they can get confused when they get tons of reports or pages showing similar things…
Tip #5 The meaningfulness: think about the message rather than graphics
Once you let people into the tool like PowerBI, the effect could easily end up being Picasso-alike analytical painting with many colors but really not much value in it. In a matter of seconds, you can produce any number of beautiful charts showing any number of data pieces like a well-operated assembly line. Yet, PowerBI reporting canvas is like PowerPoint slide – it’s limited – no scrolling or paging can make you feel… limited. But that’s the whole point, the time you spend in PowerBI should be spent on trying to fit and visualize the information in that space so that it is clear and easy to digest by potential users at a first sight.
It is especially important when you consider that PowerBI has two display areas:
- Dashboard – primary point where users go, but no filtering or interactions – dashboard tiles are only links to underlying reports and their purpose is to present the current status of things.
- Reports – analytical spaces with all interactive capabilities and their purpose is to dig into data details to understand the reasons why certain things happen
Feeling dizzy? What do we really want to see in here?
Luckily, this is only the demo dashboard presenting product capabilities rather than anything of real use. This is a bad practice example as all tiles in this dashboard show pretty much the same data (opportunity count and revenue) just from a different angle. Which makes this more analytical than the status view. Consider, how this can be simplified to put focus only or important things:
Not only you can see it better, but you also have more space to put other (meaningful!) things. If you want to know more about the data displayed, you just need to click on any of the tiles in the dashboard and you will get to the report where you can see all the data from the original dashboard:
So, the rule of the thumb is: put less but only meaningful stuff. Remember that the information you want the user to get is the most important, not the overwhelming number of data views in all possible dimensions. It should be clear at the first sight whether there is a problem or not, whether you need to investigate further or have a peaceful moment to grab a cup of coffee.
The concepts presented above are very basic but you can use those tips when creating reports which should be simple and easily understood by regular users. I collected them here as they are also built on our experiences, which we gained when designing analytical reports for our company and now are successfully used by people across project management, finance and development practices. All thanks to simplicity, focus on the users’ needs and spending more effort in figuring out what should be the most efficient way to tackle the particular piece of data and then create the report.
So remember: it’s easy to create PowerBI report, but it’s a little harder to create a meaningful report! Contact us to make sure you have only the best ones!