How to leverage Microsoft’s Power BI
Bryan L. Smith, CPA, CITP, CISA | August 2021 Footnote
Editor's note: Updated July 29, 2021
Throughout our careers, we come across concepts and ideas that have major impacts on our jobs and profession. One of those ideas came early in my accounting career and that was Lotus 1-2-3, an electronic spreadsheet application that ran on an IBM PC.
At the time, we used pencils, columnar paper and 10-key adding machines on audits to create our workpapers. It was a constant battle writing, erasing and recalculating these paper-based spreadsheets. Lotus 1-2-3 was a simple-to-use electronic spreadsheet that forever changed the accounting profession due to the ability to easily make changes and because it performed instant recalculations. Microsoft further enhanced the electronic spreadsheet with Excel, and, today, most accountants use Excel daily.
The next such idea came in 2010 when Microsoft launched an add-in to Excel called Power Pivot. I quickly realized that Power Pivot would have as much of an impact on the accounting profession as did the electronic spreadsheet. Today, Microsoft has expanded their offering of data analytic tools into a suite of products under the heading of Power BI. This includes Excel add-ins Power Pivot, Power Query, Power View and Power Map. It also includes the standalone Power BI Desktop, the cloud app PowerBI.com, the mobile app Power BI Mobile and the on-premises Power BI Report Server.
The reason these tools have transformed data analytics is because they put the power in the hands of the end-user and not the technology department. For accountants, this means you now have the power at your fingertips to perform advanced data analytics.
Building your relationships
The heart of the Power BI tools is the “data model.” The data model is included in Power Pivot and Power BI Desktop and is a collection of tables and calculations. The collection of tables get connected together through relationships in what I refer to as the “VLOOKUP killer.” No longer do you need to use VLOOKUP to put all your data in one table. Instead, you connect the tables with a relationship that allows you access to data in all tables. For example, in Image 1, if we have a Sales table with sales transactions that includes Customer ID and Product ID but not the Customer Name or Product Name, we can include the Customer table and the Product table and relate them to have access to the Customer Name and Product Name.
We no longer need to use VLOOKUP to add the Customer Name or Product Name to the Sales table.
The calculations in the data model are referred to as “measures” and use the Data Analysis Expression (DAX) functions to build multi-dimensional calculations that are virtually impossible in standard Excel.
Excel calculations are based upon rows and columns, where DAX calculations can leverage relationships and advanced functions to create specific calculations based on many variables. For instance, you can create a measure that calculates sales for a specific product group for a specific customer location and add it to a PivotTable that includes year, which then creates a calculation of specific product for a specific customer location by year:
In Image 2, this PivotTable displays the internet sale of bikes in the U.S. by year and color. There is no need to add slicers or filters to perform the calculation. In addition, this measure can be used in the calculation of any other measure. For instance, you can create another measure that would be “InternetSalesOfBikesInUS” divided by total sales to get a percent of total sales represented by bikes sold in the U.S. via the internet. This example just scratches the surface of the overall capability of measures in the data model. For your organization, think of metrics that, if you had them, would allow you to make better decisions:
- Revenue per new customer.
- Number of repeat customers.
- Largest under or over budget by department.
- Month-to-date, quarter-to-date, year-to-date compared to last year’s same period.
- Store revenue compared to budget variance.
Your mind should be racing through the metrics that are most important to you and the fact that you can now create that calculation directly within the data model without the need to copy and paste data or create calculations based upon other calculations in order get those metrics in standard Excel. “If I only knew ‘X,’ I could make a better decision,” is now a reality.
Representing your data
Once the data model is created with the collection of tables and established relationships and the various measures, Power BI Desktop and PowerBI.com allow you to create visualizations that represent your data. As accountants, we love our rows and columns of data, but it is often difficult to spot trends or anomalies within all the rows and columns. Thus, modern-day data analysis has turned to visualizations.
For instance, take this simple table in Image 3. Can you spot the trend?
You may be able to; however, how much easier is it to spot the trend in Image 4 when this same data is in a visualization?
In this visualization, we can immediately tell there is an increase, then a dip, then back to an increase. This is the exact same data as the table, but it is much easier to “visualize” what the data is telling us.
Power BI Desktop and PowerBI.com give us the tools to use our data model and build interactive visualizations that help us to better understand our data. The visualizations go beyond the traditional line, bar and pie charts, better representing our data. Visualizations like maps, funnels, doughnuts, treemaps, gauges, scatters and many more, including custom and third-party visualizations, make it very easy to create visualizations by simply selecting the field and displaying the data immediately in a recommended visualization. Select a geographic field and it will select the mapping visualization, making the creation of visualization quick and efficient.
Visualizations are placed on pages, and, by default, all visualizations become interactive with one another. Interactivity means that when you select a data element in one or more visualization, the other visualizations filter or highlight based upon the selection, as seen in Image 5.
This interactivity allows you to drill down into the details to further analyze your organization’s data.
Learning never stops
Since 2010, Microsoft has continued to enhance the Power BI tool set and deliver applications that allow any accountant the ability to perform complex data analysis with ease. It’s imperative to stay updated with the ongoing changes to best perform in your role.
Bryan Smith is the virtual CIO of CPA Crossings and serves as a trusted adviser to CPAs in public accounting, as well as business and industry, on all aspects of information technology. Bryan’s expertise includes in-depth knowledge of paperless workflow solutions, the impact of technology on internal controls, strategic IT infrastructure design and information security best practices.
Related CPE
Visit www.mncpa.org/catalog for details and registration.
21WC-0413: K2’s Advanced Topics in Power BI (Webinar)
Aug. 23 | Noon–2 p.m. | 2 CPE
21WA-2288: K2’s Business Intelligence, Featuring Microsoft’s Power BI Tools (Webinar)
Aug. 28 | 7:30 a.m.–3:10 p.m. | 8 CPE
21WX-1466: Power BI: Introduction to MS Power BI Tools (Webinar)
Sept. 7 | 8:30–10:30 a.m. | 2 CPE
