The Absolute Guide to Dashboarding and Reporting with Power BI: How to Design and Create a Financial Dashboard with Power BI – End to End
()
About this ebook
Related to The Absolute Guide to Dashboarding and Reporting with Power BI
Related ebooks
Supercharge Power BI: Power BI is Better When You Learn To Write DAX Rating: 5 out of 5 stars5/5DAX Patterns: Second Edition Rating: 5 out of 5 stars5/5M Is for (Data) Monkey: A Guide to the M Language in Excel Power Query Rating: 4 out of 5 stars4/5Power BI Data Modeling: Build Interactive Visualizations, Learn DAX, Power Query, and Develop BI Models Rating: 0 out of 5 stars0 ratingsFinancial Modelling in Power BI: Forecasting Business Intelligently Rating: 5 out of 5 stars5/5Power Query for Power BI and Excel Rating: 0 out of 5 stars0 ratingsBeginning Microsoft Power BI: A Practical Guide to Self-Service Data Analytics Rating: 0 out of 5 stars0 ratingsMicrosoft Power Platform A Deep Dive: Dig into Power Apps, Power Automate, Power BI, and Power Virtual Agents (English Edition) Rating: 0 out of 5 stars0 ratingsCreating Data Stories with Tableau Public Rating: 0 out of 5 stars0 ratingsCreating Business Applications with Office 365: Techniques in SharePoint, PowerApps, Power BI, and More Rating: 0 out of 5 stars0 ratingsBeginning Power Apps: The Non-Developer's Guide to Building Business Applications Rating: 0 out of 5 stars0 ratingsPro Power BI Desktop: Self-Service Analytics and Data Visualization for the Power User Rating: 0 out of 5 stars0 ratingsData Analysis for Corporate Finance: Building financial models using SQL, Python, and MS PowerBI Rating: 0 out of 5 stars0 ratingsCleaning Excel Data With Power Query Straight to the Point Rating: 5 out of 5 stars5/5Excel Dashboards and Reports Rating: 5 out of 5 stars5/5Excel Power Pivot and Power Query For Dummies Rating: 3 out of 5 stars3/5Implementing Power BI in the Enterprise Rating: 5 out of 5 stars5/5Power BI for the Excel Analyst: Your Essential Guide to Power BI Rating: 0 out of 5 stars0 ratingsPower BI A Complete Guide - 2021 Edition Rating: 0 out of 5 stars0 ratingsSupercharge Excel: When you learn to Write DAX for Power Pivot Rating: 0 out of 5 stars0 ratingsData Visualization with Excel Dashboards and Reports Rating: 4 out of 5 stars4/5Microsoft Power BI A Complete Guide Rating: 0 out of 5 stars0 ratings
Enterprise Applications For You
Excel Workbook For Dummies Rating: 4 out of 5 stars4/5Excel : The Ultimate Comprehensive Step-By-Step Guide to the Basics of Excel Programming: 1 Rating: 5 out of 5 stars5/5Bitcoin For Dummies Rating: 4 out of 5 stars4/5Scrivener For Dummies Rating: 4 out of 5 stars4/5Enterprise AI For Dummies Rating: 3 out of 5 stars3/5Managing Humans: Biting and Humorous Tales of a Software Engineering Manager Rating: 4 out of 5 stars4/5QuickBooks Online For Dummies Rating: 0 out of 5 stars0 ratingsQuickBooks 2024 All-in-One For Dummies Rating: 0 out of 5 stars0 ratingsExcel Tables: A Complete Guide for Creating, Using and Automating Lists and Tables Rating: 5 out of 5 stars5/5Excel Formulas That Automate Tasks You No Longer Have Time For Rating: 5 out of 5 stars5/550 Useful Excel Functions: Excel Essentials, #3 Rating: 5 out of 5 stars5/5Excel 2019 For Dummies Rating: 3 out of 5 stars3/5Excel Gurus Gone Wild: Do the IMPOSSIBLE with Microsoft Excel Rating: 4 out of 5 stars4/5Excel 2016 For Dummies Rating: 4 out of 5 stars4/5Mastering Excel Macros: Introduction: Mastering Excel Macros, #1 Rating: 4 out of 5 stars4/5Mastering Salesforce CRM Administration Rating: 5 out of 5 stars5/5Learning Oracle 12c: A PL/SQL Approach Rating: 0 out of 5 stars0 ratingsAccess 2019 For Dummies Rating: 0 out of 5 stars0 ratingsSharePoint For Dummies Rating: 0 out of 5 stars0 ratingsMrExcel XL: The 40 Greatest Excel Tips of All Time Rating: 4 out of 5 stars4/5Learn Windows PowerShell in a Month of Lunches Rating: 0 out of 5 stars0 ratingsMicrosoft Power Platform A Deep Dive: Dig into Power Apps, Power Automate, Power BI, and Power Virtual Agents (English Edition) Rating: 0 out of 5 stars0 ratings101 Most Popular Excel Formulas: 101 Excel Series, #1 Rating: 4 out of 5 stars4/5PowerShell Troubleshooting Guide Rating: 0 out of 5 stars0 ratingsPersonal Knowledge Graphs: Connected thinking to boost productivity, creativity and discovery Rating: 0 out of 5 stars0 ratings
Reviews for The Absolute Guide to Dashboarding and Reporting with Power BI
0 ratings0 reviews
Book preview
The Absolute Guide to Dashboarding and Reporting with Power BI - Kasper de Jonge
The Absolute Guide to Dashboarding
& Reporting with Power BI
by
Kasper de Jonge
Holy Macro! Books
PO Box 541731, Merritt Island, FL USA
© 2019 Kasper de Jonge
All rights reserved. No part of this book may be reproduced or transmitted in any form or by any means, electronic or mechanical, including photocopying, recording, or by any information or storage retrieval system without permission from the publisher. Every effort has been made to make this book as complete and accurate as possible, but no warranty or fitness is implied. The information is provided on an as is
basis. The authors and the publisher shall have neither liability nor responsibility to any person or entity with respect to any loss or damages arising from the information contained in this book.
Author: Kasper de Jonge
Tech Reviewer: Adam Saxton
Publisher: Bill Jelen
Compositor: Jill Cabot
Cover Design: Alexander Philip
Indexing: Nellie J. Liwam
Published by: Holy Macro! Books, PO Box 541731, Merritt Island, FL 32954 USA
Distributed by: Independent Publishers Group, Chicago, IL
First Printing: February 2019. Printed in USA
ISBN Print 978-1-61547-057-0,
Mobi 978-1-61547-140-9,
PDF 978-1-61547-240-6,
ePub 978-1-61547-363-2
Library of Congress Control Number: 2019930607
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
Preface
The first version of this book focused on using Power Pivot and Excel for dashboarding and reporting. After I finished that book in 2014, I was determined not to write another one: Writing a book is hard . As the popularity of Power BI rose, Bill Jelen nudged me to write a new version of the book, focusing now on using Power BI for dashboarding and reporting, but I put it off. Now, almost four years after Dashboarding and Reporting with Power Pivot and Excel was published, I am nearing completion of Dashboarding and Reporting with Power BI.
What made me decide to finally update the book? Well, frankly, Power BI is now ready for it. Over the past couple of years, we on the Power BI team have created a new product from scratch, and it finally has all the features needed to re-create Excel dashboards and reports—but with Power BI. Another great motivator is the fact that I am still getting great feedback on the first edition of the book.
I have been in and out of the business intelligence industry for the past 15 years. These years have brought fundamental shifts in the way we work. We have gone from full-fledged IT-centric reporting to now enabling business users to work together with IT and also enabling everyone in an organization to work with data.
When I met Power Pivot in 2009, I immediately fell in love. As soon as I installed the first beta of Power Pivot, I knew the business intelligence world that I worked in would change forever. Now that Power BI has come along, it is possible to create insights without being a business intelligence professional; you just need to be familiar with Excel. BI professionals and business users alike are enthusiastic for Power BI.
I hope you will find this book very useful in creating dashboards that provide insights into data, and I’m looking forward to seeing you out there in the Power BI community. You can find me at my blog, http://www.kasperonbi.com, and on Twitter, at http://twitter.com/kjonge.
How I Got Started with Power BI
Today I work on the Microsoft BI team, which creates amazing tools that allow every Excel and business user in the world to gain insights into data. This is the story of how my love of Power Pivot brought me to work at Microsoft.
I have been passionate about computers and IT from the moment my parents bought me a Commodore 64 in 1988. When I started going to a school that focused on IT, I actually started paying attention, and my grades finally started going up. Ever since then, I have been glued to computers.
Working
on my Commodore 64 in 1988. Look at that wallpaper.
My first jobs were not in crunching data or getting numbers to people using Excel. I was riding the tail end of the dot-com bubble in the late 1990s, building websites. I’ve always had an affinity for trying to make sense of large amounts of data, but I had no idea there was a whole world out there that did this for a living—or that it had a name. I fondly remember that somewhere along the line, I tried to use HTML and SQL Server 6.5 to create a report that contained several charts. I continued going down the development path, using SQL, .NET, and ASP.NET while living in the weapon of choice for every developer: Visual Studio.
In 2004 I made a career switch to a DBA/developer role, where I was introduced to data warehousing. I was hooked. Here I was also introduced to the tools that go on top of data warehouses, such as Cognos PowerPlay, which allows users in a business to analyze the data in their organization. I realized that, thanks to BI tools, users were able to get their own profound new insights. They were enthusiastic about being able to work with such data for the first time.
When I decided I wanted to see some different companies, I tried my hand at consulting and moved back into a developer role. But I kept trying to get work that allowed me to give data to users in any shape or form. After about two years, I wanted back into business intelligence and managed to talk my manager into sending me off to an Analysis Services course. That five-day crash course in building multidimensional models was my introduction to Microsoft BI. After that, I largely focused on using cubes and reports to build BI solutions, as well as on occasional data warehouse jobs. I became a typical BI developer, working on long projects to deliver value to business users who usually had to wait some time to get the data they needed. They often came to my desk, asking for new calculations or additions to the models because they did not have the capability or tools to do it themselves. I wasn’t really an Excel user, but I worked closely with business users (typically ones who did use Excel) to make sure they got the information they needed. While I was doing this work, I also started blogging, mostly to keep track of my findings for later reference. I still maintain that blog, at http://www.kasperonbi.com.
One day in late 2008, I heard about a new project called Gemini that would allow business users to gather and analyze their own data directly inside Excel (see http://ppivot.us/SEUSO). I was intrigued with this revolutionary technology that would bring the power of the complex cubes world directly to Excel users. This new product would make it possible for anyone to use Excel to load millions of rows of data from multiple different places and combine the result into one report with ease. It seemed like science fiction to me then.
In August 2009, I finally got a chance to play with Gemini (http://ppivot.us/O1NUW). I was awed and in love: Gemini made it easy to quickly build reports that had before taken hours.
Then, in November 2009, my eyes were really opened, when I was introduced to the language that was underneath it all: DAX (http://ppivot.us/v3ThX), an incredibly powerful language that enables users to do a lot with ease.
Around the same time, I found a partner in my Power Pivot explorations: Rob Collie (http://ppivot.us/aqdx8). We spent many nights trying to figure out how Power Pivot worked and trying to find cool new things we could do with it. It was a pretty amazing time. I started trying to convince my manager that Power Pivot was a great tool and that we should use it in our day-to-day work with customers—and I was starting to get traction.
In June 2010, I attended TechEd in New Orleans. Rob Collie and many other folks from the Microsoft product team were there, too. The conference was a frenzy of Power Pivot discussions. It seemed like this was the only thing the entire BI community could talk about. I had many discussions with Rob about Power Pivot, and near the end of TechEd, Rob said, I’m leaving Microsoft. Why don’t you take my job at Microsoft? I think you would do great.
I was stunned. I’d never thought that was possible and dismissed the idea pretty quickly.
After some talks with my wife, I decided to send Microsoft my resume. A few weeks later, I was interviewing with the team, and about four months later, I worked my first day at Microsoft, helping designing features for Power Pivot for SQL Server 2012. I was able to make a living working on the product I love. Pretty awesome!
Notes and Tips
This book covers a lot of different topics, written as a story about a user named Jim. Throughout the story, I often dive deeply into various subjects, call out certain areas, and give tips. To do this without deviating from the story, I make heavy use of notes as well as tips that fall into four categories:
Data Model Tip
Visualization Tip
Power BI Tip
Power BI Desktop Tip
At the end of the book, I provide an index of all these tips so you can easily find them at any time.
Hyperlinks
Throughout the book, I reference sites and blog posts for further reading, including my blog, Kasper on BI, Microsoft online help, and others. Because hyperlinks can be very long, I have used a URL-shortening tool to create shortened links, such as http://ppivot.us/SEUSO rather than http://www.powerpivotblog.nl/project-gemini-building-models-and-analysing-data-from-excel-memory-based-dimensional-model/. If you are reading a physical copy of this book, make sure you pay attention to the capitalization as you type the URLs because they are case sensitive.
Samples, End Results, and Data Sources
This book describes how to build reports and dashboards based on a Microsoft Access database. Some readers might find it valuable to follow along with the book and build the project themselves, and others might want to see and play with the end result themselves. I have therefore uploaded all files to my website for you to download: http://ppivot.us/sampl32d. All the measures used in this book are available at http://ppivot.us/daxfile.
Acknowledgments
A book is never written alone, and so many people contributed to this book in both small and big ways that it’s nearly impossible to write a complete list. Many users of Power BI both inside and outside Microsoft, bloggers, tweeps, conference attendees, and, of course, the Power BI development team helped me shape the book.
Of course, there are some people I need to especially thank for their help because without them, I wouldn’t even be in the position to write a book. I thank Rob Collie for putting me on this crazy journey in 2009, when we were trying to understand DAX during the Project Gemini timeframe and he urged me to change my life by going to work for the Analysis Services team and moving to Redmond.
I also have to give thanks to the true masters of DAX—Howie Dickerman, Srinivasan Turuvekere, Jeffrey Wang, and Marius Dumitru—for creating DAX in the first place and helping me each time the DAX became too magical. Finally, I thank Adam Saxton for helping out by doing a tech review and getting rid of those pesky bugs.
This book wouldn’t have been possible without the help of Bill Jelen as publisher, Kitty Wilson for editing, and Alexander Philip for the awesome cover design.
Finally, I want to thank my family, Mom and Dad, for getting it all started with that first Commodore 64. And of course I thank my beautiful girls—Anouk, Karlijn, and Merel—for putting up with my crazy passions and allowing me