Dashboarding & Reporting with Power BI

How to Design and Create a Financial Dashboard with Power BI – End to End

Kasper de Jonge

• Publisher: Holy Macro! Books
• Year: 2018
• ISBN:
  9781615473632
• Edition: 1
• Topic: Business
• Subtopic: Business Intelligence
• Index: Business

Microsoft's revolutionary Power BI is a tool that allows users to create and transform data into reports and dashboards in new and much more powerful ways.This book, written by a member of Microsoft's Power BI team, provides a practical step by step guide on creating a financial dashboard. The book covers in detail how to combine and shape the relevant data, build the dashboard in Power BI, providing layout and design tips and tricks, prepare the model to work with fiscal dates, and show values used in many financial reports, including year-to-date, variance-to-target, percentage-of-total, and running totals reports.

1 - Introduction

This book is a follow-up to my earlier book, Dashboarding and Reporting with Power Pivot and Excel, which covers building reports and dashboards using Power Pivot for Excel. Now, four years later, the book you’re currently reading shows how to build reports and dashboards using Power BI instead. This book is a little different from most books already out there on Power BI. It doesn’t cover all the features of Power BI, nor does it cover the DAX language extensively. Many other books do those things well. A few good examples are Bill Jelen’s PowerPivot for the Data Analyst, Rob Collie’s DAX Formulas for PowerPivot, and Matt Allington’s Super Charge Power BI: Power BI Is Better When You Learn to Write DAX.

This book is intended as a very practical book to help you get started on a Power BI journey and bring your data analysis skills to the next level. This book follows Jim, a business user who is very familiar with Excel, on his journey to create a financial dashboard and complementary reports in Power BI. The journey starts with Jim finding out what information his organization needs to understand the current rhythm of its business. He then gathers the needed data for presentation in a dashboard, for which he must determine the best ways to visualize the information. As you follow Jim on this journey, you will use Power BI Desktop and DAX formulas to solve several very common business calculations, such as year-to-date revenue, variance-to-target, and year-over-year growth.

You will also watch as Jim creates reports in Power BI Desktop to allow those in his business to dive deeper into the numbers. Then you’ll see how to share those workbooks using Power BI.

In many places, this book dives deeply into subjects such as the Power BI analytical engine, DAX formulas, and Power BI and dashboard design tips and tricks.

What Is Business Intelligence?

Before you get hands-on with Power BI, it’s important to look at why the tools discussed in this book even exist.

Business intelligence (BI) has traditionally been used as an umbrella term to refer to software and practice that should lead to better insights and decisions for an organization. Instead of making decisions based on gut feeling, an organization can base its decisions on actual facts it visualizes by using business applications. Many Excel professionals are likely to think, “Hey, that’s what I’m doing every day, but I don’t give it a fancy name!”

BI gained traction in the 1990s, when companies started creating and collecting more and more data but couldn’t get the information into the hands of the business users to create insights and make decisions based on that information.

Building BI solutions has traditionally been the territory of IT organizations and consulting firms. It has often resulted in very heavy-weight and expensive projects that are highly curated and complex.

A data warehouse collects data from all over a company and consolidates it into what many think of as “the single version of the truth” for data. An IT organization may want all data to flow through the BI system to make sure it’s consistent and non-redundant, in order to gain “correct” insights.

To make the data in a data warehouse actionable, organizations have often created cubes on top of the data warehouses. They have optimized those cubes to gain fast access to the data for doing quick analytics on large amounts of data. Then, on top of those cubes, canned reports are created to help users get insights into the data. In the 2000s, Excel improved this situation with the addition of PivotTables, which allow users to drag and drop data from a cube straight into Excel.

Today, the stream of information that flows through an organization comes not just from BI systems but also from cloud-based solutions like Microsoft Dynamics, Salesforce, and Internet of Things devices, as well as, of course, the number-one BI tool in the world: Excel. Users from the business side of an organization—without help from the IT side—can create reports directly at the source. These reports often bypass a BI solution completely or mash up data from the data warehouse with additional data retrieved from sources such as the ones just mentioned. This often leads to clashes between IT and business users because IT folks want the data to come from their BI solution, but the business cannot wait for IT to provide that information. The world does not stand around and wait for data to become available. Events happen all the time, and it is often crucial for an organization to react quickly.

As the pace of the world has increased and as more and more data has become available to organizations, CFOs and other stakeholders in organizations have wanted to get insights into data more and more quickly. BI was traditionally set to create insights through long projects, but that type of system makes it hard to quickly get insights into the ever-changing data. When and after the financial crisis hit in 2007–2009, the business world had to make many cutbacks, especially in the IT space. So, at the same time that IT departments were being expected to provide oversight of and more insights into data, they were being given fewer resources they could use to consolidate larger amounts of data.

But an organization doesn’t need to rely on just its IT department for data. An army of business users in any organization know Excel and also know the data inside out, and they are very proficient at creating reports and using data to gain insights. Microsoft recognized this and thought that perhaps business users and IT could work together to serve the information needs of the organization and use each other’s strengths instead of competing. In 2006, Microsoft began an incubation project called Gemini, named for the constellation. The twins in this project are IT and business users, working together.

Microsoft started its BI journey in 1994 by creating the very successful product Microsoft SQL Server Analysis Services (SSAS), which is designed for developers with an IT background. It is the best-selling analytical database engine in the industry. The idea behind Gemini was to shape the world-leading BI product SSAS into something that fits in Excel and can be used by Excel professionals. The Gemini incubation team aimed to determine whether it would be possible to empower Excel professionals and at the same time have them work together with IT. The team wanted to figure out how to put more business intelligence into the hands of the business users and allow them to “self-serve” the information.

The Gemini team determined that it needed to create a product with a few radical features:

• The ability to work with massive amounts of data: Since SSAS had hit the market in 1994, a lot had changed in the IT industry. Importantly, PCs had gotten more powerful, and memory had gotten much cheaper. For the Gemini team, this meant that the product would need to work on the data and optimize it for analytics use in Excel. Whereas Excel 2010 and earlier allowed a user to work with 1 million rows of data, the Gemini team wanted a product that would allow users to work with very very large amounts of data directly in Excel—much larger amounts of data than anyone could have dreamed of before. The team thought that working with 200 million rows of data should be like a walk in the park.
• The ability to create a single PivotTable that combines data from two separate tables without writing a single VLOOKUP(): One of the most common uses of Excel is combining data from several separate data sources into a single report. In traditional Excel, you need to use the complicated Excel function VLOOKUP to combine the data into a single table. In Power Pivot, you can leave the data in separate tables and just create a relationship.
• The Data Analysis Expressions (DAX) language: DAX, which is designed for analytics, is based on the Excel formula language and even shares some functions with Excel. At the same time, it’s very different from the Excel formula language: Whereas the Excel formula language references cells in a worksheet, DAX references tables and columns.

These three changes together have brought a lot of power to the fingertips of many Excel users. As Bill Jelen (also knowns as MrExcel) describes in his book PowerPivot for the Data Analyst (http://ppivot.us/5Vqxd), “There are two types of Excel users: People who can do a VLOOKUP with their eyes closed and everyone else....Suddenly, hundreds of millions of people who (a) know how to use a mouse and (b) don’t know how to do a VLOOKUP are able to perform jaw-dropping business intelligence analyses.”

Project Gemini wanted to bring the power of SSAS to a billion users of Excel—right on their desktops. This is referred to as “personal BI” or “self-service BI.” But project Gemini was meant to be more than an add-in for Excel. It was meant to provide “team BI” so that a workbook shared with team members using SharePoint would retain all the interactivity but could be used by many users at the same time, through a web browser—no Excel required. The idea was that the data in a workbook could be refreshed via an automated schedule so that new data would be added to the workbook with no work needed. Another benefit of sharing workbooks to SharePoint would be that it would allow IT to govern the data shared onto SharePoint.

In October 2009, Gemini was renamed PowerPivot for Excel, and it first shipped with Excel 2010 (http://ppivot.us/5Vd7u). It was quite clear that PowerPivot would radically change both business intelligence and Excel. Shortly after the release of Excel 2013, Microsoft added a space to the tool’s name—Power Pivot (http://ppivot.us/ifdYe). Power Pivot today is still available for Excel 2010, Excel 2013, and Excel 2016. However, in Excel 2016, the name Power Pivot disappeared, and the functionality, which remains the same, became a native part of Excel.

Microsoft’s latest entrant in the BI world, Power BI, was released in 2015. Built on the success of Power Pivot, Power BI has truly revolutionized the business intelligence world. Whereas business users were the primary audience for Power Pivot, Power BI is accessible enough that all users in an organization can use it to make sense out of all the data at their disposal. Many businesses no longer just rely on data sources locked away on premises but also use cloud-based data solutions (for example, Dynamics 365, Salesforce, Marketo, Google Analytics). These cloud solutions allow users to run their marketing or sales businesses online, and they enable users to get started with a ...

Table of contents

• Preface
• Acknowledgments
• 1 - Introduction
• 2 - Understanding Dashboards and Reports
• 3 - Collecting and Preparing the Data
• 4 - Building the Main Report
• 5 - Building Detailed Reports
• 6 - Sharing Dashboards and Reports Within an Organization
• Index