Microsoft Power BI Portfolio in a Day

Welcome to our first full-length portfolio piece walkthrough.  In this video, we'll be building a really cool report based off of a fake marketing research data, which you can find attached to this video in the resources section.

A quick review of what we will be focusing on in our MarketMindz research project, including an overview of the most important questions we need to answer.

An overview of the MarketMindz dataset to familiarize ourselves with the table and quickly browse through the column names and data types.

In this video, we'll use the Power Query Editor to perform a variety of basic data cleaning and preparation transformations that will help us get ready for visualization later in the lesson.

A deep-dive into some data visuals that can help us summarize which campaigns are over/underperforming and which products and platforms are generating the most sales.

A deep-dive into some data visuals that can help us summarize which campaigns are over/underperforming and which products and platforms are generating the most sales.

A deep-dive into some of the best visuals that can help us understand who the buyers of our products are, ranging from demographic elements like Marital Status and Education to Household Composition and Income.

In this video, we'll unlock the power of the Key Influencers visual to help us understand what attributes in our data might be driving purchasing behavior around a specific campaign.  

Switching from Power BI to PowerPoint now, we'll discuss some of the decisions and thought processes that go into choosing a great color palette for your report.  Remember:  You don't necessarily have to use the colors I pick, feel free to get creative!

With our color palette selected, let's start designing a layout that will make our report really pop. 

Obviously this video has nothing to do with business intelligence but if you're the creative type or just eager to learn, join me for a video where I walk through some techniques for designing a relatively quick-and-dirty company logo in PowerPoint.

Now that most of the heavy lifting is done in PowerPoint, let's talk about how to actually get these designs out of PowerPoint and into Power BI. 

In the following videos, we'll be integrating the design elements we picked in PowerPoint with the data elements we built in Power BI, hopefully creating the perfect synthesis or data and design... or at least something that hopefully isn't hideous.  We'll start with Campaign Performance!

In the following videos, we'll be integrating the design elements we picked in PowerPoint with the data elements we built in Power BI, hopefully creating the perfect synthesis or data and design... or at least something that hopefully isn't hideous.  We'll start with Campaign Performance!

One down, two to go.  Now, let's turn our attention to prettying up our Buyer Composition page.

One down, two to go.  Now, let's turn our attention to prettying up our Buyer Composition page.

Wrapping up our MarketMindz Market Research project by finishing the final design touches on our Purchase Drivers page.  Note that the completed .pbix file and background slides are attached to the last video!

Wrapping up our MarketMindz Market Research project by finishing the final design touches on our Purchase Drivers page. 

In this video, we'll do our final pass of the report, searching for any additional data visualization mistakes we may have missed and search for other ways to elevate the insights we want to make sure our users take note of.

In this guide portfolio piece walkthrough, we'll be building an infographic to highlight sales performance for 3 new Kraken Koffee locations Florida and learning how to build some pretty complex designs in Power Point for our reporting work.  Dataset attached!

Note:  When recording this tutorial, I used a CSV version of the dataset that Udemy was unable to upload due to a bug.  I attached an XLSX version of the file here.  Feel free to use it or convert it to a CSV file when following along.  The file type won't make much difference once you import it into Power BI.

Let's kick off this tutorial by exploring the Kraken Koffee dataset and modeling it into a schema that will help us build a fast, high-performing report. 

A quick overview of our completed model, including an overview of the relationships and model structure.

In this video, we'll evaluate the story-telling and insight possibilities in our dataset and build out some measure tables before we take a stab at some light coding, using DAX.

We'll kick off our first DAX practice with some extremely basic DAX measures featuring some basic aggregation functions, things like SUM, AVERAGE, and COUNT that are common calculations from which to build more complex expressions.

Next, we're going to take things up a notch by playing with a couple of really useful intermediate functions in DAX, RANKX and ALLSELECTED.

This isn't strictly-speaking a DAX course, however, so if you found the previous video a bit too challenging, here is a completely DAX-free solution for obtaining the same outcome.  Also, don't forget that I have a free full-length relatively brief series of DAX videos available at my YouTube channel for Sweatpants BI if you need more background/context for some of the expressions we're writing.

Now that we have most of our calculations pre-prepared, let's start building some data visualizations to help us capture the most important insights from our Kraken Koffee dataset.

Next, let's build out some basic time-series visuals to help us identify when the busiest times of the week are for our coffee shop locations.

In the next group of videos, we'll walk through how to do some very basic projections in Power BI to help us understand where sales may end up at year's end in order to give our Kraken Koffee sales teams a full-year revenue target.

Did someone say 'Give me more DAX'???  ... No?  Well, we're doing it anyway.  In this video, we'll be doubling down on some great DAX techniques for manipulating our calculations so that we can build out a very, very basic revenue forecast for the remainder of the year.

Next, let's build our own custom label for our forecast visuals.

In this video, we'll wrap up our forecast lesson by rounding out the rest of our data visuals before we turn our attention to fleshing out the presentation of our infographic.

Getting to pick our own color palettes for data visualization and report designs is fun but, for better or worse, it's not something you'll always have the license to do.  Oftentimes, our employers will have their own branded color palettes and guidance on how to apply those colors, sometimes even with regard to visualizing data.  In this video, we'll discuss a scenario where the colors are provided ahead of time and walk through how to apply them to a custom PowerPoint theme.

Note:  I have attached a PPT with Kraken Koffee collateral for you to use.  

Next, with our colors established, let's build out the shape of our infographic and start laying down some basic design concepts to carry through our presentation.

The Power BI card visual is great but it's far from the only way to present important numbers in Power BI.  In this video, we'll design our own cool KPI cards in PowerPoint, which we will integrate with our Power BI measures later in the lesson.

Next, let's cut out some areas in our layout for some of the narrative elements we're going to include our infographic to explain some of the most important takeaways that readers should gain from our infographic.

Our infographic is already looking pretty sweet (in my opinion, at least), but it's not exactly nautical so far.  This is Kraken Koffee, we're talking about.  Sea monsters!  The high seas!  Treasure chests!  Let's add some minor cosmetic elements to our presentation to help create an oceanic sense that will give our infographic personality, hopefully without distracting from the presentation of our data.

A layout like this is too pretty to leave in PowerPoint.  Let's get this background out of our slide deck and into Power BI so that we can start bringing over our data visuals!

In this video, we'll start integrating the design and thematic elements of our infographic layout with the data visuals we created earlier in the lesson, starting with our summary section.

Next, we'll run through the same exercise, this time focusing on our time-series visuals.

Finally, we'll bring over our forecast visuals and update the style and presentation of those to match our theme.

As we wrap up our infographic, let's flex our AI muscles with some practice using Power BI's Smart Narrative visual, an exciting way to integrate narrative text with dynamic data-driven calculations that make sure our infographic is as informative and compelling as possible.  We'll kick things off with the Summary section.

Next, let's get some more practice with the Smart Narrative visual, this time focusing on our Time-Series section.

Great!  Now, let's wrap things up by rounding out our narrative elements with the Forecast section of our infographic.  Note:  The completed version of the .pbix file is attached to this video.

In this video, we'll be building a more complex Power BI tool to help our users quickly slice & dice and better understand a huge number of insights about home prices in Austin, Texas and which property features drive or influence listing prices around the city.  You can find the dataset, as always, attached to the resources on this video!  There is also an image of the official flag of Austin, Texas, which I use later in the lesson.

Note:  When recording this tutorial, I used a CSV version of the dataset that Udemy was unable to upload due to a bug.  I attached an XLSX version of the file here.  Feel free to use it or convert it to a CSV file when following along.  The file type won't make much difference once you import it into Power BI.

As always, let's kick things off by starting in a blank .pbix file and importing our data.  Then we'll explore our housing insights dataset and determine the fields that might help us surface some interesting findings about the Austin, Texas housing market.

Next, let's pop over to the Power Query Editor and clean up our dataset so that it's prepared for modeling and visualization.  I'll also walk through some techniques for keeping your source data and cleaned data connected but separated so that you can reference the uncleaned data should the need arise.

In this video, I'll run through a common technique that I use for modeling fact & dimension tables and creating keys to hook everything up in the report builder so that you can visualize data across multiple tables.

Next, we'll focus on creating a table specifically for our data attributes that describe local school information around our Austin, Texas properties and perform some additional data transformations that could come in handy later in the course.

Now, let's finish modeling our dimension tables so that we can move on to more exciting things.

In this video, we'll cover a few more modeling tricks in Power Query Editor that can help you parse out large text fields and I'll show you the huge impact that good modeling can have on the size of your model and even the size of your report.

One of Power BI's most annoying default settings is its stubborn insistence that users prefer cross-highlighting over cross-filtering.  In this video, I'll show you how to change this setting because I'll be referencing cross-filtering interactions for the remainder of the lesson.

In this video, we'll walk through the pretty cool enhancements of Power BI's New Card Visual and begin setting up our Summary Insights data visuals.

With our KPI cards out of the way, let's go ahead and wrap up our Summary Insights.

Real estate is about one thing:  Location, location, location.  Because of that, this dataset is full of useful geolocational data like longitude/latitude attributes, zip codes, and more.  In this video, I'll walk through how to use the Map visual in Power BI and point out some important features to keep in mind.

In this video, we'll continue to flex our geographic data muscles but this time I'll show you some techniques for maximizing the value of mapping complex data to help users identify geographic patterns, focusing on the fields in the School dimension table we set up earlier in the lesson.

In the next group of videos, we will be building out some views focused on understanding the relationship between home prices and different features listed for each property in the dataset.  First, we'll use Field Parameters to try to understand the relationship between price and several of the numeric fields in our dataset that describe different home amenities.

In this video, we will circle back to the descriptive text table we parsed earlier in this tutorial and try to visualize how different words are used to describe houses in different price categories to see if listing agents tend to use different words to highlight home features at different price points.

And, finally, let's use Power BI's built-in Key Influencers visual to try to understand what's really driving house prices in our dataset.

Picking a color palette may not be the most exciting thing in the world but it is crucially important from a design and user experience perspective.  Color is a powerful tool for helping us ensure that users see what they should see and it's important to make sure the colors we choose will treat all users equally.  In this video, I'll walk through how to pick a color palette for this tool and get creative with our theme.

Once we have our colors picked, we can start designing a layout that will help us balance our presentation, the information we need to explain to users (typically via text boxes), and the data visuals that contain our most important insights and takeaways.

In order to make our data visuals sync with our design, we'll need to get PowerPoint and Power BI on the same page.  In this video, we will quickly carry over our color palette and background image to Power BI so that we can begin integrating our design and data visualizations.

Now we can start making our data visuals look incredible by incorporating our design elements with our data visuals.  This is going to help our reports look extremely polished and professional while also helping to elevate the impact of our data visuals.

Next, let's go through the same design integration exercise, this time using our Location view.  We'll also continue exploring an extremely vital aspect of Power BI user interaction:  Bookmarks.

In this video, we'll apply our design and theme to the school data visuals we created.  We will also circle back and fix a common sorting issue that can result when creating custom groups.

Finally, let's quickly update our Features view and pull those visuals into our main report so that we can move onto some really cool interactivity features that will help us round out the user experience for our report.

With our bookmarks set up, let's create some custom buttons that will give our users a way to easily navigate back and forth between the four different views we've built for our report.

In almost any BI tool, users are going to want to get their hands dirty and play with the data.  That's a huge part of why we build Power BI reports in the first place.  In this video, we will build a customer slicer panel that users can activate and hide when they're finished, applying filters that will carry throughout their exploration.

Another awesome way to help your users grow more excited about interacting with your report is to introduce a report page tooltip.  In this page, we'll walk through how to set one up so that the bubble map in our Location view is even more engaging to use.

Though not always 100% necessary, cover pages can be extremely useful for setting the scene for a Power BI report.  Just like the "Star Wars" movies always start with that big scroll of yellow text to get you ready for what you're about to see, a cover page can help you provide useful context, navigation, tips, and background instead of just dumping your users into the middle of the data.  In this video, we'll design a quick cover page to help our users navigate to the most important views quickly.

In this portfolio piece, which is a more practical, real-world business scenario, we're going to work with a fake company's employee data to identify trends in three very common HR measures:  Headcount, Retention, and Turnover.  The datasets are attached so once you have those downloaded, let's get to work!

In this video, we will explore our HR dataset and get to work transforming and modeling it for our report.

Since there will be a fair amount of DAX involved in this lesson, let's quickly set up some measure tables to help us keep all of our calculations organized.

People come and go from companies all the time.  In this video, we'll flex our DAX skills to build some calculations to help us monitor the change over time in employee headcount (which is just business speak for the number of employees, in case you're wondering)

Another really important HR metric is employee retention or the number of employees who chose to stay at the company during a specific timeframe.  Keeping talent is critical for a company's success so this measure helps companies evaluate whether they're doing a good job at that.

Turnover, on the other hand, helps companies quantify how much of their workforce is leaving over a specific timeframe and how much of that loss is voluntary (I got a better job) or involuntary (I got fired/laid off).

In this video, we'll walk through some ideas for visualizing our headcount measure.

Next, let's create some visuals that help quantify employee retention.

And, finally, let's build some data visuals that help us capture the story of employee turnover at the company and hopefully identify some areas where turnover could be improved.

In this video, we'll discuss some ideas for choosing a great color palette for this HR report.

Next, let's put together some design ideas that will serve as the background layouts for our report.

Now that we have our designs and our color palette, it's time to carry our PPT work over to Power BI and get to work integrating our data visuals with our theme.

With our Headcount view out of the way, let's shift our focus back to our Retention view.

And now, let's cap things off with our Turnover page.

In this video, we'll build another interactive slicer panel to help organize our filters so that they're accessible but also don't take up real estate on the page that we need for data visuals.

Next, let's build an interactive tooltip page to help our users interact with the visuals we've made and help them identify insights dynamically and quickly by engaging with our report.

There are tons of great ways to design a cover page and cover pages are fantastic places to flex your creative muscles without having to worry as much about damaging the impact of your data visuals.  In this video, we'll cover another idea for designing an impactful cover page.

Note:  The completed .pbix file is attached to this video.

Before we close things out, let's briefly talk about how to publish a Power BI report and run through one option for making your portfolio available to prospective employers.

Thank you so much for completing the Microsoft Power BI Portfolio in a Day course!  If you enjoyed the class, don't forget to leave me a review and check out my Sweatpants BI YouTube channel.  Best of luck in your Power BI learning journey and keep building incredible tools!