The best tools you need in your toolbox for Power BI

As a business analyst, you need more than just basic knowledge of building financial reports, whether in Power BI or another software.

Below you’ll learn about the best practices that the world’s top companies are successfully adopting.

You will learn the tricks to create attractive and insightful financial reports. We will look at Sales vs. plan performance dashboards, cost vs. budget analyses, income statements, and forecasts.

The 4 questions every report should answer.

You’re probably often dealing with similar datasets like the one below. This is a typical sales dashboard with a dataset that includes different dimensions and data fields. For example, you can see things like the breakdown of sales by geography and by time, indicating there’s a calendar behind in this model. Product categories, product sub-categories, products, customers, customer segments, and other elements.

So, it’s a typical sales dashboard above, but does it really get the message across? Is it understandable? Is it actionable? Can you answer certain basic questions about the sales performance? For example, the first very trivial question, is my sales performance good or bad?

It’s hard to say, despite having all the breakdowns by geography, product categories, and more. A chart like this makes it essentially impossible to answer a straightforward business question. Why? The key problem here is that there’s no comparison to the previous year or planning. There is no context of the sales here. So, if you cannot observe the variances, if you cannot really compare the categories in your data, and if you can’t do this in a simple visual way, then your dashboards will fail.

Second question: How good or how bad? Are my sales on target? Above target? Again, I don’t know.

Third, why is the sales performance as it is? Difficult to tell.

And the final, perhaps the most important question, “What are we going to do about it?” Unfortunately, this dashboard contains no actionable information that will tell you that you’re 30 percent below the plan, the reasons, and where you should focus your attention on that, and this is what you should do about it.

So, these are the key four questions that are like a litmus test for your dashboards and reports. Your reports are understandable and actionable when you can answer these four questions fast for any report or dashboard page.

The 4 questions you should ask yourself for any dashboard or report page:

• Is my performance good or bad?
• How good / how bad?
• Why is my performance as it is?
• What are we going to do about it?

The business analyst’s toolbox

To answer these 4 questions, you need to equip yourself with proper tools that every business analyst should add to the business analyst’s toolbox. These tools include, in no particular order:

1. Advanced charts

Power BI has a lot of visuals. It has everything from pie charts to treemaps, map visualizations, clustered column charts, etc. However, it does not offer advanced business charts – charts where you can compare actual to plan or actual to forecast or actual to the previous year, all of which are a basic requirement in any business analysis.

2. Variance analysis

Most of the advanced charts will enable you to perform variance analysis. Clear and understandable variance analysis is probably one-half of every business dashboard or report you’re creating.

3. Top N + Others

You may also need more advanced filtering. You can filter, for example, the top five elements by value in Power BI, but you can’t really do things like Top N + Others. This feature allows you to set the number of top elements you want to display along with the element called “Others“, which will combine all remaining elements into one. This is an important little tool you should deploy in your dashboards, but it’s tough to do in Power BI. It takes some more advanced DAX that you have to build into your Power BI model to deliver that.

4. Bridge charts with Top N

We’ll talk about waterfall charts, especially bridge charts with Top N, where you’re not just shoving all of your data at once. Instead, you can actually ensure you’re only rendering the most important changes, the most important elements that contributed either positively or negatively to your total result. A good bridge chart with Top N elements, either positive or negative, is a great business analysis tool in Power BI dashboards and reports.

5. Grouped tables with variances

A serious business analyst needs simple basic tables or metrics in Power BI, but clear tables with variances and several groups. For example, first, you should present monthly results or month-to-date results for a particular period, then the next group for year-to-date results, and another group at the end, where you’re presenting full-year results. In the full-year results, you should also present forecast vs. plan, whereas in the current period, you’re probably presenting actuals versus plan.

6. Bridge charts with subtotals

This is especially useful when creating an intermediate result within your chart before the final result. This chart helps you show the difference and explain how a certain value changes and what the causes are for that specific change. This is particularly useful with EBIT and EBITDA. To do this, you need waterfall charts with subtotals and a very flexible way to present and calculate them.

7. Displaying forecasts

Too often in Power BI and other tools, the business performance analysis focuses on data already in the past. But we forget that in reporting, we should also look into the future and determine what is going to happen? That’s why it’s wise that you bring your forecast estimations, latest estimates into Power BI and to present them very clearly in your reports and dashboards.

8. Income statement / P&L / Price calculations

If you’re presenting income statements, P&L’s or similar calculations, then you need additional tools like vertical waterfall charts that we call calculation charts. These can be quite challenging because P&L’s and income statements have mixed datasets consisting of positive KPIs, such as revenue, and negative KPIs, like costs, expenses, that need to be subtracted. This is complicated on its own but can get very complex very quickly if you decide to add things like subtotals.

To learn more about creating income statements in Power BI, here’s our Definitive Guide to Income Statements.

9. Price-volume-mix variance analysis

Variance analysis is one of the most fundamental and decisive tasks of every business analyst. PVM variance analysis allows you to calculate how your product price volume and mix changes affect your revenues. You can use this analysis for individual segments or categories within your company.

To learn more about price-volume-mix variance analysis, refer to our tutorial here: Price Volume Mix Analysis: how to do it in Power BI and Excel.

10. Small multiples

As a business analyst, you are always challenged by how much data and visuals you should insert into a report. Add too much, and the end-user won’t be able to make head or tail out of it. Add too little, and you might miss crucial information to act upon. So how do you strike that balance?

One method to both present more information and improve insight is called small multiples. This allows you to display more charts within a single visual and increase information density.

Read our extended tutorial here: Supercharge Your Power BI Dashboards with Small Multiples if you’d like to learn more about small multiples.

Variance analysis in tables

In this article, we presume that you, as a business analyst, already have some basic understanding of Power BI. But if you are just getting started or would like to become more proficient with Power BI first, you can check out our knowledge base that contains informative articles, videos, and examples for Power BI.

To better showcase the impact of each tool, we’ll be working with Zebra BI visuals. Most, if not all, of these examples can be recreated with native Power BI visuals and you can download a free version of Zebra BI Charts and Tables from App source or directly from Power BI.

If you’d like to know more about how Zebra BI makes Power BI better, read this: Why add Zebra BI visuals to Power BI?

We will start with a combination of tables and charts showing sales by business units.

Variances are hard to do in Power BI

A basic example of variance analysis would be comparing Actual to Plan values. The easiest solution would be simply placing the columns of those values next to each other and calling it a day. The problem with those basic side-by-side charts is that you are not explicitly visualizing the gaps. While it’s important to show individual values, so the end-users understand which is the largest business unit and how others compare, you must also display the gap between values. Only then can you answer basic questions, such as whether you are achieving your plan. That is really hard to do if you just have two different colored bars standing next to each other.

If you switch the chart to Zebra BI Tables, it automatically calculates and creates variances for you. Even more, the visual response to the space available and adapts the information displayed accordingly.

The great thing about having your variances shown is that you can sort by them to identify the best and worst-performing business groups quickly.

Another thing you would typically do in a table is to build a hierarchy by adding multiple fields into a Category. The users will be able to expand and collapse the tables to get to the details. This allows you to group your results into different subgroups. For example, you might have two groups – Personal care and Electronics, each with individual divisions, such as Mobile, Video, and Computers. The Mobile group consists of tablets and smartphones.

Tip: expose the first two levels in the initial view by default. From here, the user can simply click to drill-down to get into the details.

The next thing in a typical table-based report would be creating multiple comparisons like comparing the Previous year alongside the comparison to Plan. You then get more columns that you can arrange according to your preferred pattern, like first comparing the Previous year before the Actual value, followed by comparison to Plan. Or you can place absolute values, followed by absolute variances and relative variances. In the end, the main goal is to make it as clear as possible and for it to fit your column-structure standard.

Designing Zebra BI variance tables

Now the only thing left is to polish the design of your tables. In Zebra BI, you can switch the visualization in each column, such as changing the format from bars into a simple value column with numbers. Sometimes you can even hide certain columns if you don’t need all the measures in your tables. Simply click on the column heading and select the Hide column option. This way, you keep the data within the table; you just decide not to show it.

With tables, they must be readable, so you need to make sure you have enough space for the charts and other data. So one of the many things in Zebra BI that will help you control this experience is responsive visualization. It means that depending on the visual size, the program will adapt the number of values shown and how they are displayed.

You can adjust the Minimum chart width value in the Chart settings section to determine when the data is still being rendered before it collapses or changes format to better adapt to the new size. Just be careful not to make it too small, or the charts will look too squeezed together. Like with everything, you need to strike the right balance.

Further customizing your tables.

Another feature to further customize your tables is the Group functionality. For example, you can use it to show both year-to-date and monthly results in the same visual. In our example, we insert our Period calculation field into the Group placeholder. The period calculation is the data field that contains all the period options. This is much faster than having many separate visuals to present fresh information since Power BI now only triggers one query.

Similarly, instead of using month versus year-to-date values in your tables, you could just insert certain elements from your calendar dimension for a different view of your data.

If you have well-prepared calendar information, then you can, for example, take your Quarters field and insert it into the Group placeholder to analyze your data by quarters. A word of caution, though: if you put too many elements in your columns, you will have too much information. It is important to keep things clean and not waste unnecessary space with unnecessary information.

A good way to save space is to ensure a clean display of data labels. For example, most of your values could be in thousands, so zeros or the letter K are displayed across your table. Instead, just use Zebra BI to display the letter K in the title. The result? The entire visual is more legible and easier on the eyes. The columns are narrower, and you can present more information. In business reporting, you will often present data in thousands, millions, billions, etc. So always keep the labels in mind.

This setting is in the Data labels pane in the “Show units in” field.

Totals and Subtotals

With Zebra BI, you can show the Grand Total of the full year with just one click. You can do that with the + icon, where you can add additional columns.

The feature is fully customizable. You can change the names of columns directly inside the visual or hide the columns you don’t need. When hiding columns, you have granular control over hiding them in a single group or across all groups (see image below). To unhide it, click on the + icon again, and a list of all hidden columns will appear, along with the one you seek. Next, click on the column’s name, and it will appear once again within your table.

The next stage would be also to build a hierarchy in your groups. Again, this is possible in Power BI because of the multilevel grouping. For example, if you add the month data under the Group field’s quarters, you’ll also show months aggregated to the quarters.

To do this, just add Quarter and Month measures to your Group placeholder.

You can then collapse these quarters and only show the last one that concerns you the most.

Whenever you have a hierarchy of at least two levels in the Zebra BI table, you can also show the subtotal. Here, the subtotal is just the result for the one quarter, which is then displayed along the months.

Show the variance versus plan as a percent of the plan

What if you want to show relative variance as a percentage of the plan instead of the increase/decrease in the plan? For example, instead of showing the value to be -9% below the plan, you present it as 91% of the plan instead. Some specific cases require you to use such notation, especially in project management. While there’s no specific function for that, you can add your own custom index. You calculate your index and then just put it into the Values placeholder in Power BI, and it will appear as an additional measure. This is what we call custom-calculated columns. It will appear as a distinct measure.

To see how this works, you can open our Zebra BI – Income statements Power BI file that’s included with downloads at the bottom of this tutorial.

Below is a typical example of an income statement with the previous year, actuals, and variances. But to finish the report, we would also like to add the percentage of revenue, which you can calculate using DAX. You can download our PBIX file and try it for yourself.

If you’d like to learn more about data modeling in Power BI, here’s our tutorial: Top 5 Power BI DAX tricks for super effective Power BI dashboards.

Once you calculate it (already included in our sample file), take this new measure and place it in the Values placeholder, below the AC value. Now it just acts like any other column, which you can then rename and move around. You can also further format each column by giving it a border or a background color. In the end, the P&L report should look something like this:

Changing views in Tables

Zebra BI tables allow you to design two views. The first view is what we call the more visual chart view. The second one is called table view that provides more numbers and other data. In this way, end users can first focus their attention on the most important information, like the variances, and then read the full tables and numbers they need.

This is a great compromise; show the visual design first, with fewer columns, but still access all values with one click. This easy-to-access information change by chart slider is one of the best practices you can apply to your own reports.

Variance analysis in charts

Let’s talk about charts and the various analyses you can do with your visuals. Compared with tables, charts are more often used when you break down by month or work with a time component. Time should run left to right, so use charts with a horizontal axis whenever you have a time series.

When it comes to presenting single measures, native Power BI does its job well enough. Typical examples include market share, HR reports, and simple dashboards presenting data within a certain time frame. But even then, you can do something like show the comparison to the first point in the chart. Zebra BI Charts, in this case, offer you multiple options to showcase your data and focus on what is most important.

But where native Power BI falls behind is with multiple measures. A typical example is a comparison between Actuals and the previous year. By default, Power BI neither visualizes the gap between the measures nor adds any additional labels. You would need to fiddle around with the settings, but even if you managed to get the right labels and other elements in place, this will still be just a normal side-by-side chart.

Variance analysis with waterfall charts

Below is a typical example made with Zebra BI Charts. It’s the basic waterfall (bridge) chart, where you have the totals for the previous year and for actual. On the right is the growth rate compared to the previous year, which is already calculated. All the elements are completely interactive, so if you click on the growth rate label, you can choose to show absolute variance, relative variance, or both.

You can also cut the axis to zoom into the variances if you want people to just focus on the variance. Additionally, you can choose in the settings how much you break the axis.
What does that mean? The percentage shows how much of the total value you are showing. So if you set it 50%, you’ll only show the upper half with the variances, while everything else will be below the chart.

Tip: if you want to show just the variances, cut the axis to 100%.

Available types of variance charts

Zebra BI offers several useful charts in addition to the waterfall chart, including a bar chart with variances, area charts, and line charts.

The great thing about these visuals is that they have smart responsiveness built-in. Take the bar visual, which starts with the bars for values and two additional charts for absolute and relative variances. If you shrink it, separate charts for variances are removed and integrated into bar charts. This is what we call integrated variance charts. Of course, when you resize them back up, additional charts appear again.

However, you can always set the chart responsiveness manually. For example, you might not want the responsive chart in your dashboard, so just open Chart settings and set the Layout from Responsive to another option, such as Integrated variance or Absolute Variance or similar.

Adding another dimension (small multiples)

Now that we have a great-looking chart with variances between actual and previous year’s results, we want to analyze data by groups. With default charts in Power BI, that’s almost impossible to do elegantly. Instead, you have to fiddle around with drill-downs and barely intelligible charts that tell you very little.

In Zebra BI, you can use scaled small multiples. Small multiples is a visualization method that puts all charts of different categories on a single page and scales them so that you can easily see which ones are the most important and analyze the trends.

You can easily see that the Skincare business unit is the least important. If you want to review it, you simply click that part of the visual, and it opens up as a full chart that’s easier to analyze. It’s a great way to first look at the forest and then look at every individual tree.

When working with small multiples, you have to clean things up a little so they are easier to read. This usually means adjusting the Data labels density. For example, you have the setting First, last, min, max, which only displays the first and last values and the largest and smallest value in between.

Another important feature is the Top N setting. This shows only the categories that have the most impact on your business. In the Visualizations pane, select the small multiples category and enable the Top N feature. Instead of showing 20 business units, it can only display the top 8. But instead of just cutting off the other categories, it bundles them into the Other category, which is the sum of all other categories.

Zebra BI gives you plenty of control over small multiples. You can set grids, change axis labels, colors. However, we recommend that you set the Layout setting to Auto because it intelligently sets up your small multiples to make the best use of space.

Looking great on mobile

Small multiples also look great on mobile. Zebra BI will automatically adjust them, so you only need to open the Mobile layout on your View tab and drag and drop a Zebra BI visual onto your canvas.

You can even change Zebra BI layouts on mobile.

Advanced features for business analysts

When it comes to business analysis, waterfall charts are fundamental, and sometimes it’s nice to have the ability to show subtotals in the chart. For example, when currency fluctuations impact your EBIT, you might want a waterfall chart that shows your EBIT before the impact of currency fluctuations.

You need to create a table with all of your results already calculated in a simple flat table to achieve this. Next, click your chart and set your EBIT elements as results. To do this, right-click on them and mark them as Results. You can then mark any of your elements as results and have complex calculations in your chart.

Waterfall charts can also be used for Price-Volume-Mix analyses, where you can quickly see how price, volume, or product mix affect your sales.

Subtotals in tables and hierarchy

You can do similar things in Zebra BI tables, such as Profit and loss statements and income statements. First, let’s look at an income statement, where Revenue is the first subtotal of product and service revenue.

This is very easy to do. Just right-click the name and select Result entry from the menu.

When a certain value is a cost, you simply select Invert to show it has a negative contribution to the final result (or subtotal). This is called row-level invert, giving you complete control over individual entries in your dataset.

Forecasts functionality

Forecasts come in handy when you want to look at data for the full year before you actually have that data. Instead of comparing actuals to your plan, you need to compare the forecast to plan.

The trick is to add all three values to the visual: Actuals, Plan, and Forecasts, which you should drop into their respective placeholders. Then you simply click on your visual and hide Actuals from the Full-year group of data.

You can also add forecasts to your charts. This will allow you to view actual results up to the current month and then forecasts onward. Like this:

For this to work, fields with actual results in your data need to be empty. Then, zebra BI will automatically know what to do with it.

These are the tools you need

If you are a business analyst, this post will surely help you move beyond confusing charts and into a world of intelligible charts that tell a story.

Instead of combing through data, you can use these tips to create reports that pinpoint trends, identify weaknesses, and show the strengths.

While Power BI is a powerful tool, it is more powerful with Zebra BI.

With Zebra BI, you need minimal effort to implement advanced waterfall charts, small multiples, and income statements that make sense. Some of these things are much harder or even impossible to do in vanilla Power BI.

Posted by Ziga Milek

Source: Zebra BI