How Power BI can supercharge your Excel data (part 3)

Third in a three-part series on how Microsoft's Power BI is challenging Excel.

For the last couple of decades Excel has been the most popular tool to convert raw data into reports and charts. This could change soon as more people start to use Microsoft’s new Power BI. 

Power BI – charting made easy

In the last post we created a few simple reports. I am assuming you have read that post because the techniques demonstrated are directly applicable to creating charts. You drag and drop fields (columns) to the Visualization area to create the charts.

This post covers creating charts and concludes with a discussion on publishing reports.

The correct term is a graph but Excel calls them charts and that is the term I use to describe the graphical representation of values.

You can convert an existing report into a chart simply by selecting the report and then clicking a different Visualization icon. You can try a few different charts to see which chart best visualises your data.

Professional Development: Conference On Demand - Unlock Excel: discover fresh and exciting ways to unlock the full potential of your data.

New Visualizations are being added regularly, including charts. Even if you can’t find the perfect chart there is a marketplace that enables you to import new Visualizations. Many are free.

Any Slicers in place also affect charts. One powerful feature of Power BI is the ability to use a chart Visualization to filter the report – to demonstrate that we need a chart.

Converting a report to a chart

Let’s convert the Account report we had from the previous article into a State report so it is smaller.

Charts in Power BI are like PivotCharts in Excel – in general smaller reports make better charts.

I have dragged the State field to the Rows section and removed the Account field – see image below.

Figure 1

Now that we have a reasonably small report we can convert it into a chart. The tricky part with this report is that we have dollars and a percentage. Plotting both on the one chart will present a challenge.

Fortunately there is a Visualization that has a column plotted on the left axis and a line chart plotted on the right axis, allowing us to plot both $ and % on the same chart.

With the report selected, click the Line and clustered column chart icon, as show below. If you point to a Visualization, it will display its name.

Figure 2

Initially it won’t work – the column is shown, but the percentage is not set to the line chart. You need to drag the %GT Amount to the Line values section – see the two images below.

Figure 3

Figure 4

The Line chart isn’t quite the way we want it. It looks like there is large drop for VIC (third column). That is caused by the scale of the right axis starting at 24% rather than zero.

Always be wary of an axis that doesn’t start at zero, as it can make changes appear larger than they really are, as the example above shows.

To fix the right axis we need to use the Format (Paint Roller) option. In the Y-Axis section you will need to scroll down to the Y-Axis (Line) section and Enter 0 in the Start box – see image below.

Figure 5

Now that we have our State chart we can filter the other Visualizations on the page by clicking on a State column in the chart.

In the image below I have clicked on the WA column (far right). Note that the other column colours have faded and the multi-row card now only shows WA.

Figure 6

This feature allows you to combine reports and charts and it allows readers to interact and focus on certain numbers that may be outliers or unusual. By including charts you are in effect also including a built-in filter feature to the dashboard.

If you click the three dots in the top right corner of the chart you will see the sorting options. See image below.

Figure 7

In general if there is no sequence involved in the data set then sorting the data can make it easier to read. I have sorted the chart above, and WA has moved to the far left as it is the highest value.

Limitations

In Excel you have almost total control over your chart’s format. That is currently not the case in Power BI. For example I can’t find a way to increase the font of each axis. You can change elements such as colours.

Publishing your report

This is where things become more complex.

This link explains the distribution options.

At the moment the output options are:

  • Publish to the web (not an option for organisational reports, as it is public)
  • Share a dashboard (you can enter email addresses that can access the dashboard – to see it users will need Power BI)
  • Create a dashboard in a group (requires a Pro license – see below)
  • Publish a content pack (requires a Pro license – see below)
  • Print or save as a PDF

All of these options require you to publish the dashboard from the Power BI Desktop to the Power BI web service. You must sign up to the Power BI web service before you can publish to the web from the Desktop version.

The content pack seems like a great solution, but it requires a Pro license for all users. (Well, everything has been free up until this point, so Microsoft has to make some money somewhere.)

At the time of writing, the Power BI Pro license costs about A$15 a month per person. I am not sure about enterprise costs.

You can easily publish a dashboard to the net, but obviously that has inherent confidentiality issues.

To publish your dashboard follow the prompts from the Publish icon on the right of the Home ribbon, see image below. Note: this will be public.

Figure 8

Final thoughts

Getting the reports out to only those who should see them is an issue at the moment.

Many people don’t like the idea of their data going into the cloud. There is currently a technical preview of Power BI on Premise, where you can run Power BI behind your own firewall without needing the cloud. Stay tuned for developments on that front.

If you are a business analyst or management accountant I recommend you learn Power BI now and start to use it. It is the future of reporting.

All other accountants need to consider:

  • Do you get data from multiple sources?
  • Do people email you values or reports that are in existing data source?
  • Do you have to manipulate existing data sources?
  • Do you have to combine multiple files to create your reports?
  • Do you have to filter or sort your data?

If you answered yes to any of these, then Power BI or at least Excel’s Power Query may be part of a solution to your needs.

Power Query alone is worth learning and is available in Excel – see a previous article and video here.

There is much more functionality built into Power BI and again much of it is available in Excel. The DAX function language is available in Excel via Excel Power Pivot.

If you would like me to write more posts on Power BI please comment below. Also if you have started to use Power BI or the other Power products in Excel please share your comments and thoughts below.

Neale Blackwood CPA runs A4 Accounting, providing Excel training, webinars and consulting services to organisations around Australia. Questions can be sent to a4@iinet.net.au

This is part 3 in a three-part series that shows how you can use Power BI to get the most out of your data. Read Power BI part 1 here and Power BI part 2 here.

Author’s Note:

Here’s an example of how quick things are changing in the Power BI world. I wrote the articles in January and created the videos on Thursday 9th February. The day before (US time) the February update was released – see link below.

There are three February updates that affect some of my comments in the videos.

Chart Axes – in the latest update you can change the font size.

Quick Calc – they have added two extra options (there was only one option at the time of recording).

SharePoint – there is a new option to publish to Sharepoint - not sure how it works yet.

So keep an eye on the Power BI blog for more regular updates.

Read next: 9 handy Excel tips that work


Like what you're reading? Enter your email to receive the fortnightly INTHEBLACK e-newsletter.

April 2019
April 2019

Read the April 2019 issue of INTHEBLACK magazine.

Each month we select the must-reads from the current issue of INTHEBLACK. Read more now.

CONTENTS