Power BI offers many advanced functions for data analytics. However, you do not have to be an expert to use it. In fact, it can be a very useful tool regardless of your knowledge of data analysis – which is what it should be! Here are 5 tips on how to make the most of Power BI reports.
A great tool for report building
Power BI 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 them yet – shame on you! I recommend keeping an eye on the Microsoft Power BI 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 what you’ll find here). I assume you have touched upon this technology at least a bit. At the same time, you’re probably not a hardcore analyst, as you would most likely know all these tips already.
This article will not tell you how to do all the things you possibly could with Power BI. In fact, you should try it for yourself, it’s very intuitive and allows to build very advanced visualizations. Once you stumble across a challenge, you should look up answers online on how to approach it.
That’s exactly what we did here at Predica – we’ve built a companywide analytical reporting tool 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.
However, we have already invested much effort into building and maintaining proper data sources. Therefore, I’d like to share some experiences we’ve had. I will also share little hints we use in creating reports both for us and for our customers.
Tip #1: Simplicity – don’t go too fancy with visualizations
Following the idea of delivering a message… There is an increasing number of visualizations available in Power BI which you can get from the gallery. Some of them are pretty complex. They can show you the relations between data elements in a unordinary way that can make sense… quite rarely (for example, if you’re a hardcore analyst).
Selecting the right chart for your data
For most of us ‘ordinary people’ – and I’m saying, probably 98% of us – 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 what 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 pie fields which have similar values.
Let’s try to report the sales volume per region – try telling whether red or orange is bigger or by how much they differ:
Isn’t this clearer?
The general rules of thumb about visualizations
- Remember that people usually read from top left to bottom right, so put the most relevant stuff (KPIs?) where users go first
- Vertical bars – for general data display. Avoid rankings, use sorted data, it’s easier to read
- 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 it works just as well
- Bar/line mixed – to present two values of different types (like money and percentage)
- Bubble – to present 3 different number values (two axes and bubble size).
Tip #2: Context – interrelations between elements
One of the coolest features of Power BI is the cross-filtering capability. 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.
Using filters in Power BI
But what might not be so obvious at first sight, is that you can actually use three ways of filtering and connecting data to make your analysis experience better and easier.
Let’s consider the project management example. You may be interested in seeing the time reported by people (top bar in the below example) and the time reported each month (the bottom bar). There you can see the different behaviors the interactions provide.
Types of interactions:
No filtering happens between elements. Use it 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:
The filtered value is displayed in the context of the total. Use it when you want to show how much of the total the selected element forms. In the example – clicking on the bar in the top chart fades out the bottom chart. Only the part of the bar which is applicable to the clicked element remains highlighted:
Displaying the actual filtered value. Use it when you want to see what actually hides behind the selected element. Here 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 on which relationship you select.
Additionally, when there’s a lot of data elements, it might greatly influence the ease of use of the report, especially for not advanced users (who we usually create such tools for).
Click here to find more info about creating interactions between visualizations.
Tip #3: Divide and conquer (or slicing and dicing) – filters
It’s the most basic concept of data visualization, yet you might still be surprised by how many filtering possibilities there are in Power BI reports. Here are 5 obvious ones.
Basic report filters panel:
- Visual level filter – filters 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
- Page level filters – apply to all elements on the page
- Report level filters – apply to all pages which can be particularly useful when a user is supposed to journey through the pages to see the data in the same filtering context, but with a different view presented on 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:
- Slicers (in-canvas filters) – filters available as single or multiple selection checkboxes or dropdowns. I haven’t found them particularly useful. They take up the canvas space and, considering cross-filtering capabilities of most visualizations, do not provide much value added. Also, like the page level filters, they work only on a particular page. This in the majority of cases I worked with was rather limiting. The reason is that when you go to a different page, you lose the context of the data you worked with.
- 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, a checkbox list you create a vertical chart, you can use it just for filtering – just click the bar to filter out everything else:
Again, let’s consider the project management example. You can think of having a 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 in the above example).
So, if you use in-canvas filters, you need to select the project you are interested in on each page individually. However, 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 per month, 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. However, you will then end up managing and supporting a large number of such cases. Alternatively, you can be clever and design a report in a way that can be used by both. And this is where hierarchies come in handy.
Using hierarchies in Power BI
There are three ways to use hierarchies:
- They can come from the data source (typically OLAP/Tabular-like), so basically present in the data model
- They can simply be based on date and time data – here Power BI does a nice thing for us and allows to present any time data as a Year/Quarter/Month/Day hierarchy (more here)
- Or you can put more than one dimension in the visualization. It doesn’t make them visible but allows to drill from one to the other.
Once you have some, 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 views.
Since it’s easy and fast to create reports in Power BI, 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: Meaningfulness: Think about the message rather than the graphics
Once you let people into a tool like Power BI, the effect could easily end up being a Picasso-like analytical painting with many colors but really not much value to 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, Power BI reporting canvas is like PowerPoint slide – no scrolling or pagination can make you feel… limited. But that’s the whole point! The time you spend in Power BI should be spent on trying to fit and visualize the information in that space. It should be clear and easy to digest by potential users at a first sight.
It is especially important when you consider that Power BI has two display areas:
- Dashboard – the primary point where users go to, but with 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 the interactive capabilities. Their purpose is to dig into data details to understand the reasons why certain things happen
Consider this sales opportunities example from Microsoft:
Feeling dizzy? What do we really want to see 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. This makes it more analytical than the status view. Consider how this can be simplified to put focus only on the important things – the actual opportunities’ number and volume:
Not only can you see it better, but you also have more space to add other (meaningful!) things. If you want to know more about the data displayed, you just need to click on any of the tiles to get the report where you can see all the data from the original dashboard:
So, the rule of the thumb is: include less, but only the meaningful stuff. Remember that the information you want the user to get is the most important. It’s not about the overwhelming number of data views in all possible dimensions.
It should be clear at 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 advice that you can use when creating reports that should be simple and easily understood by regular users. I collected them here as they are also built on our experiences from designing analytical reports for our company.
They are now successfully used by people across project management, finance and development practices. All thanks to simplicity, focus on the users’ needs and spending more effort on 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 Power BI report, but it’s a little harder to create a meaningful report! Contact us to make sure you only have the best ones!