If you are like me, you are still in awe about some of the major announcements made at Microsoft Build. I am excited about what is happening with Power BI in the near future and you should be too. With all of this news, people have been asking me what my favorite announcements are. I decided it was a good idea to talk about these new features and share why I think you should be excited as well!
Datamarts in Power BI
This is the headline that stole the show. We have been waiting for this solution and everyone is excited that it has arrived. In short, datamarts provide quick access to transformed data for rapid report development for citizen developers.
What makes this significant?
You might already be asking “Why do I need to care about this? I can do that already with datasets and dataflows!” We always are talking about the right tool for the job. Datamarts can provide quality data for users with a few clicks. But what makes it special is the ability to transform and build your model 100% in a browser. While you may think this is not a big deal, it actually opens up a whole group of users who have not been able to build advanced reports. While I am a Microsoft person, I still have a soft spot for Linux. And in reality, I know there are a chunk of users who love MacOS or Chrome OS. Because of this change, those users now can build out advanced reports without having to use a Windows VM to accomplish the goal.
Also, what I love about this feature is the ability to enable smaller groups within an organization to create mini data warehouses. While I am a firm believer you should engage your data teams to get assistance with your raw data, this feature enables power users to create their own solutions for lower priority initiatives. It does not replace your current strategy, but can help extend it for business units expanding their horizons.
Anything else to consider?
Keep in mind that datamarts can only be created in premium workspaces. Premium Capacity and Premium Per User both can be leveraged, but you will need to upgrade your pro workspaces. If you have not revisited your licensing strategy, it might be time to review it and make some adjustments.
When I used to work at The Hershey Company, a consultant once asked me if we were given a special version of Microsoft Office that only had Outlook and PowerPoint. Like Hershey, many companies seem to solely rely on PowerPoint to get their message across. For those organizations, the solution was to pull screenshots or export pages into their decks. But storytelling in PowerPoint changes the game.
What makes this significant?
My favorite feature with Power BI is the ability to manipulate the data on the screen during a presentation. By bringing the report into PowerPoint, you now have the best of both worlds – refined presentations paired with interactive reports.
In addition to interactivity, I love the ability to get data in real time with my reports. Everyone has weekly/monthly/quarterly meetings where they use the same template over and over again. Since you are integrating the live report into PowerPoint, you have one less slide to worry about!
Anything else to consider?
There are two main things to consider when it comes to using Power BI inside of PowerPoint.
The first one might be a blocker with your organization. You will need to install the Microsoft Power BI add-in to PowerPoint. If your organization blocks add-ins, you might not be able to use this feature. I suggest searching for this feature and submitting a request ASAP if you feel this add-in would be helpful. This way it is ready for you when you need it.
The other is permissions to the report. If you do not freeze the report, you will need to ensure recipients of the report have access to the report. Much like embedding your reports in Microsoft Teams or SharePoint, you need to grant access to the underlying content.
You can learn more about storytelling in PowerPoint here.
Express Design with Power Apps
“Wait a minute – you are talking about Power Apps? I thought this was all about Power BI?!” I know, but trust me when I say it all connects!
I recently started using Power Apps in Power BI for very specific use cases. I have experience with Power Apps, so it is not a big deal for me to quickly build one to integrate with my reports. However, if you are new to Power Apps, this feature might be a shortcut to getting started.
What makes this significant?
There are several different reasons to use Power Apps inside of Power BI. One of the easiest scenarios you might use Power Apps is for writing data back to your database. If you want to enable report consumers the ability to add records, you can embed an app that handles the transaction.
You could sketch up a form on a whiteboard or a piece of paper. Once complete, take a picture of it and load it into Power Apps. Then map the fields to your fields and you will be set. Talk about a true no code app design experience!
Anything else to consider?
The biggest consideration for your solution is licensing required for Power Apps. If you are writing back to SQL or Dataverse, you will need to license your app users or the app itself to use premium connectors. This adds additional cost to your solutions if you have not already purchased licensing.
While the three announcements above are my favorite, there are some honorable mentions as well. They do not have me as excited, but they are worth noting.
General Availability of Metrics in the Power BI Service
Originally named Goals, Metrics allows you to see your key performance indicators (KPIs) in a single scorecard. This feature has been around for a while in preview, but is finally entering general availability. You can read more about metrics here.
Power BI is coming to Outlook
I will be honest – I am not a fan of email. With Microsoft Teams, I do not feel the need to use Outlook like I have in the in the past. I really like how Microsoft has integrated Power BI inside Teams through channels and the app. This could provide a little easier passing of reports to consumers if you are heavily reliant on email in your role. You can read about Power BI inside of Outlook here.
Microsoft Build 2022 Was So Exciting!
I always enjoy hearing about new features in Power BI. It felt like Christmas came early this year with Microsoft Build providing these impactful announcements. I feel like these are announcements that only happen during Ignite.
How about you? Did you find Microsoft Build just as exciting as I did? Did you hear about anything else that will revolutionize your use of Power BI? If so, tell me in the comments below!
What licenses do I need for Power BI? When I first start working with a customer, this is usually the first question I am asked. Power BI licensing is somewhat complicated. Most people tend to focus on the price, but they forget to focus on the actual solution and goals of the organization. As a consultant, I prefer to focus on the goals of the organization and size the licensing appropriately. A single feature might not justify Power BI Premium Capacity. However, a few chained together could provide a real return on your investment.
My goal is to enable you to make the right choices when it comes to Power BI licensing to meet your budget. No one wants to buy more licensing than they need. While I cannot cover every single situation, I will address the most common licensing scenarios for content creators, modifiers, and consumers.
Power BI Licensing Options
This is where everyone wants to start. They have read the articles and have their questions. Before we dig into which one is right for you, let’s start with the different options that are out there.
Power BI Free
Power BI Free allows users to only work within their personal workspace. Unless they have been granted access to a workspace using Power BI Capacity, they will never be able to consume content outside of their personal workspace.
Power BI Free capabilities
While restrictive, you do have the potential to try reports out and build some custom visualizations with no additional cost. You might find it to be a great place to start your journey while controlling costs.
Power BI Pro
The first paid tier of Power BI Licensing is Power BI Pro. This license allows for users to interact with workspaces inside of the Power BI Service. Regardless of the role, you will need this license to work outside of their personal workspace if the organization has no Power BI Premium Capacity assigned.
Power BI Pro capabilities
Often people think they can bypass this license, but find out how they need it. You can purchase this license independently as an add-on. However, it is included with the top tier of enterprise (E5), education (A5), and government (G5) licensing packs. At the very least, you must plan for Power BI Pro licenses for your content creators and modifiers as I will explain later.
Power BI Premium Per User (PPU)
The next tier is licensing users with Premium Per User. This intermediate step allows for the features found in Power BI Premium Capacity at a reasonable price point. This is a great option for small organizations who need premium features but are trying to manage their budgets. The biggest catch with Premium Per User is that if the workspace has been designated for Premium Per User features, consumers must have a Premium Per User License. However, content published to a standard workspace can be viewed by other Power BI Pro users.
Premium Per User capabilities
A little known fact is that there is a step-up license option available for users who already have a Power BI Pro license. It essentially doubles the Power BI Pro license price for each user, but saves you from having to pay for both.
Power BI Premium Capacity
The last licensing tier is to allow users to license a workspace using Power BI Premium Capacity. Instead of licensing users in the entire organization for Power BI, you license a workspace to use Premium Capacity. This opens up content to all users from your organization to access reports without a paid license.
There is a catch – content creators and modifiers still need to be licensed to manage the content in a workspace. So if they do not have a Pro or PPU license, they will not be able to create or manage content. But the biggest benefit is that Pro users will be able to leverage premium features in the workspace without having to upgrade their license to PPU.
Power BI Premium Capacity capabilities
Premium Capacity licenses the service and eliminates the need for individual licenses for consumers. It costs more up front, but can pay off considerably.
Premium Per User or Premium Capacity?
So you have already looked at the Power BI Pricing and Feature Guide and determined you need premium features. Which option do I choose? Focusing on the end solution, I find a few factors that push you one direction or another.
Compliance and Regulatory Concerns
For some deployments, you have to use Premium Capacity regardless of other considerations. Premium Capacity has two features that are only available with this licensing option.
Multi-Geo Deployment Management
Common with highly regulated data, Power BI Premium Capacity allows organizations to store their data in a region outside of their home tenant to meet regulatory requirements. Here is an example:
Contoso Financial is based in the United States with locations across the globe. The Berlin, Germany office wants to do some analysis on regulated data that must be stored within the boundaries of their country to remain compliant based on GDPR laws. Because Contoso Financial is based in the United States, their Power BI tenant stores all of the data in the same location. To get around this, they must purchase a Power BI Premium Capacity and assign it to a region in the EU to satisfy regulatory requirements.
This feature is only available for Premium Capacity and must be used to meet these requirements.
Bring Your Own Key (BYOK)
Similar to what was outlined with Multi-Geo Deployments, some organizations need to maintain their own keys for regulatory purposes. If an organization wants to protect select data with their own key, they must use a Premium Capacity so they can assign their own key to satisfy these needs.
Data Model Size
Possibly less of a concern when just starting your Power BI journey, but data model size can play a role in your decision. With Power BI Pro, your data model is limited to 1 GB in size. Power BI allows you to increase that limit. Premium Per User increases the limit to 100 GB model size while Premium Capacity lifts it to 400 GB.
I always recommend providing quality filtering when building your data model. This allows you to keep your model lean and speeds up refreshes. It is possible to prevent the need to move to Premium licensing with a little query tuning and patience.
Consumer Audience Size
The last thing I consider when picking between Premium Per User and Premium Capacity is the size of the consumer audience. There is a tipping point where it is cheaper to use Premium Capacity as opposed to individual Power BI licenses.
If you have over 500 users who are using Power BI Pro just to view content, you will save money by deploying Premium Capacity. Using the same logic, the tipping point for Premium Per User is 250 consumers.
The biggest thing is to remember to exclude your content creators from this count. They still need a Power BI pro license to publish content!
Power BI Licensing and External Sharing
The last big question is how do I share my content with external users? There are a few options available, so it is critical to find the right choice.
Adding Guest Users
This is the easiest approach. Adding a user as a guest using Azure B2B takes a few clicks to complete. If they are accessing content in a premium capacity workspace, then your work is finished! However, if they need a license, there are two options for you.
The first option is the user brings their own Power BI license. If they have one from their own organization, it will allow them to view content in your tenant. This is great if you are inviting users from an organization that is using Power BI already.
If they do not have a license, you can simply apply one of your licenses to the guest user. Obviously there is additional cost to your organization, but for a few users it could work well.
Create a User
I am not a big fan of creating a new user in your organization. There is an additional cost to license the user and you have to maintain their account. You can do this if you want, but I feel like it is a lot of effort. Make sure you create a clear line of communication with your guest organization. As people leave the organization, their account in your tenant will not be automatically disabled. This could be a concern depending on the level of sensitivity with the underlying data.
Embed Your Power BI Content
The best way to share your content with a large audience is through Power BI Embed. If you have Power BI Premium Capacity, it is already included in your license. Using a service principal, you can authenticate and display content from Power BI inside a website. If you already have a customer portal, you can embed your report to keep it secure and easy to view.
Sometimes you might be teetering between Per User and Capacity licensing and this pushes capacity across the line. It is not much of a stretch price wise and brings a lot of additional capability.
But what if you don’t have Power BI Premium Capacity or need it? There is a little secret to enabling that external sharing at a lower price point. The Power BI Embedded service allows you to share content at a lower price point. Even better is that you can turn it on and off in the Azure portal. If you are developing your solution, you can turn it off when you are not using it to save money until you are ready to deploy it.
Bottom Line with Power BI Licensing
It is as simple as this – take some time and think through your scenarios before purchasing your Power BI Licensing. There are a lot of options and angles to consider. Keep your future goals in mind as you plan your purchase. I see customers either under spend which results in grief. Or they overspend to just get things done. Taking the time to size your environment right will pay off handsomely over time.
Did I miss any scenarios? Any questions you might have about Power BI Licensing? Did I miss anything? Tell me in the comments below!
Did you check out the May 2022 Power BI update? I am genuinely excited about this release! So many great features to check out. However, the standout favorite from this release is the introduction of field parameters. I started playing with it and quickly discovered it is possibly my favorite release this year!
To set the scene, let me give you a common scenario that I encounter. Everyone wants to sort and view their data their way. With various dimensions and measures, there are endless possibilities when it comes to designing your report. Let’s say I have three categories that I can use against six measures. I could end up creating 18 different visualizations to handle all of these combinations. But with field parameters, I can simplify that process!
Enable the Preview
At the time I am writing this article, field parameters are a preview feature. You need to enable the feature first in order to use it. Go to the files menu, select options and setting, and click on options. From there, you need to go to the preview features section and enable field parameters.
Enabling the field parameters preview feature
Finish enabling the feature by restarting Power BI. As a result you will be ready to use field parameters in your report!
Creating Field Parameters
If you follow my blog, you will find the process to create field parameters similar to what if parameters. To create a field parameter, go to the modeling tab and select field under the new parameter menu.
Adding a field parameter
Next, we give our parameter a name. To keep things basic, I am naming mine dimensions. Then I add any fields I want to use as a parameter. I set my order and click create when I am ready.
Setting up field parameters
Two new things appear. The most obvious is a slicer on the report canvas. But there is also a new table that appears in the field list. We will be using that with our visual in a moment.
New slicer and table in Power BI
With the field parameter in place, it is time to apply it to a visual. Using a simple column chart, I will put my parameter on the X Axis. Then I will add my revenue measure to the Y Axis. With this in place, I can quickly jump between my parameters.
By default, we see revenue by segmentWe also can see revenue by productAnd revenue by country
How cool is that?! We took one visual and made it easy to pivot around with different dimensions. Talk about providing context while managing your report canvas!
Do Measures Work As Well?
You bet! Similar concept applies here. You create a new field parameter like above and add measures. A new slicer is added. Once in place, I add it to my Y Axis and start manipulating my chart.
Adding measures as field parameters
And if you had a keen eye, you also might be wondering if you can do multi-select on the slicer. And if you guessed yes, then you were right!
Selecting multiple parameters on a chart
And if you wanted to go a step further, you could apply the same principle to a table or matrix visual too.
Field parameters applied to a table
Done well, you could essentially create a simple pivot table inside of Power BI that your users can manipulate with a few clicks.
The Use Case for Field Parameters
Plain and simple, the use case for field parameters is enabling you to scale visualizations with minimal effort. Based upon our three dimensions and six measures, we would need to find space for all of these charts. This solution simplifies the need to create space for all of these options.
Even without this feature, we could make something like this work. A couple of weeks ago I wrote an article on using bookmarks to accomplish this task. It works well, but field parameters make it faster and easier to maintain. If you need to make changes, you might spend additional time updating each individual visual.
You also could allow users to customize visuals in the Power BI Service. They could accomplish the same goal on their own, but there is one problem – most people don’t know how to do it! Pair that with an unfamiliar data model and it can become a recipe for disaster. This solution guides the user to use the right fields to simplify their experience.
Anything Else to Consider?
I think the only major thing left to think about is your slicer behavior. My measures were pretty similar, so it would be okay to allow the multi-select behavior. However, if I had a measure calculating a percentage, I might need to enforce a single select to avoid issues with the scale of values.
Personally, this is one of those simple enhancements that is really going to make an impact for me. How about you? Are you going to try out field parameters in your reports? Tell me in the comments below!
Tooltips are hands down one of the most important elements to your report design. When asked what I think is important about reporting, I often say you must find “The Why”. Additional context is critical to making the right decisions with data. This is where tooltips step in to provide better context with visualizations.
Now tooltips are already part of report visualizations. In fact, any measures you place on the report canvas often show up as a tooltip. But I am going a step further with this article on how to create rich tooltips that drive a better experience.
The Basics of Tooltips
The concept of tooltips are basic – we add additional measures and information to a pop-up window when we hover over a data point. These data points are generally not complex, but can add additional information. In addition, it reduces clutter on the canvas by keeping the visualizations clean.
Let’s start with charts I created in the article where I show how to add conditional formatting to column charts. Instead of creating a complicated chart, we will add context with tooltips.
Chart Showing Monthly Revenue Versus Goal
From the conditional formatting used, we can see plainly see that we missed our goal in February and March. We are just a few days into May, so we still have time to reach our target. When I hover over a bar, I can see the specific current year revenue.
Hovering to Discover Current Year Revenue in March 2022
When I select the column chart visual, I have the opportunity to include additional measures with the tooltips section. I just simply drag my CY Goal and PY Revenue measures into the tooltips section to make them visible when I hover over the visual.
Adding Measures in Tooltips
This simple addition now provides additional context to our visualizations and makes it easier to understand. We can see that we were $200,000 short of our goal in March. While not ideal, we can easily see the gap. And while it is not the prettiest, it is functional.
Next Level Tooltips
Maybe you want to add a number of measures at once to your tooltip. Or maybe you want to add some conditional formatting. Or maybe you just want to add a little style to make them look nicer. Regardless of your reason, you can create your own custom tooltips in Power BI. It takes a little effort, but it really add value to any report you create.
One of the first steps in creating custom tooltips is considering what you want to display. This is critical because you want to ensure the tooltip doesn’t become an additional report page. You also might run into slowdowns if there are too many calculations on your tooltip. Remember that less is more when it comes to quality visualizations. Once we have our plan in place, it is time to build out our custom tooltip!
Step 1 – Preparing the Canvas
The first thing we need to do is create a new page in our report. Give the report a meaningful name and then hide it from regular view by right clicking and selecting hide page. This prevents the page from being viewed when not used as a tooltip.
Next, we need to do setup on the page. On the visualization pane, we need to open the formatting section. The first thing we need to do is expand the page information section and enable the allow use as a tooltip option.
We also want to adjust the canvas size. By default, our tooltip page size matches the default of the report. That will be way too large! I expand the canvas settings section and select the tooltip page type. While I have the option to create a custom size page, I try to use the pre-defined one to avoid overload.
Setting Up Your Custom Tooltip Canvas
With all of this in place, we are ready to move on to designing the tooltip.
Step 2 – Build Out the Tooltip
This part can be a little tricky. Because tooltips are dynamic, you will run into issues where your measures might be all over the place. To make your life easier, I recommend setting a page level filter to help design and validate your data.
Next, you want to build out your tooltip measures. I have selected only four values for the sake of simplicity. If you wanted to get adventurous, you could add a chart or an embedded image. However, I have something I want to call out and this simple view will assist.
Customizing a Tooltip
Once you are happy with your design, just simply remove the filters from your page!
Step 3 – Apply Your Tooltip
Now that we have our custom tooltip, we just need to apply it to the visual. Select your visual and go to the formatting pane. Then select the general tab and expand the tooltip section. From there, double check that report page is selected as the type and pick your custom tooltip. Is your page missing? If so, go back to step 1 and make sure your page settings are correct!
Applying a Custom Tooltip to a Visual
With that in place, you can hover to discover your context! If your tooltip shows blank values, go back to your custom tooltip page and make sure the page level filters are cleared.
Custom Tooltip on the Report Canvas
And just like that you have applied your own custom tooltip to a report! You can get as fancy as you wish with your tooltips. Just keep in mind that too much can be cluttered. Here is an example of a more complex solution I did with the Dunder Mifflin Sales Database:
Jim Halpert’s Flirting with Pam has Resulted a Drop in Sales Year over Year
You can see that I was able to add some context to a simple revenue report. I could show that even though Jim’s year over year sales are down, he is trending upward with his sales. I even added his picture to make it easier to identify him.
Regardless of what you do, make sure you keep it clean and simple to make your tooltips effective!
Added Bonus to Custom Tooltips
All of this effort provides an additional benefit that you might not notice right away. When it comes to report performance, your custom tooltips might help with the loading of your report canvas.
The best way to see the clear difference is with the underlying code. I turned on the performance analyzer and pulled the query for the original chart with the standard tooltip versus the custom tooltip.
The obvious difference between the two code blocks is the custom tooltip does not load all of the measures when the page is rendered. What does that mean for your report? Simple – you can speed up the loading of the report page by pushing some of your measures to a custom tooltip! This is great for complex measures that might provide context, but are not always needed when viewing the report.
Keep in mind that if your measures are slow, it will hurt the performance of your tooltip!
Anything Else I Should Know?
Probably the most important thing to know is that sometimes these tooltips can be frustrating. They take practice and sometimes a little extra effort. But the more you do it, the easier it will become. Just keep working at it and you will be a pro in no time!
Have you made a custom tooltip before? Have you built anything adventurous? Any favorite use cases? If so, tell me in the comments below!
The word parameters is a little ambiguous when it comes to Power BI. I have written a number of articles that reference parameters, but never took the time to clarify their use cases. I wanted to dive in to the different types of parameters and understand the best way to use them. Paired with some tips and tricks, you will have a better idea of how to use all kinds of parameters in your reports.
Parameters in Power Query
In my opinion, parameters in Power Query are the most in your face. When you launch the Power Query editor, there is a button on the home ribbon that helps you manage them. But have you ever used them?
Parameters in Power Query allow you to add reusable values in your queries. The premise is simple – if you keep using the same value over and over again, why not make it easy on yourself?
How to Leverage It
To get started, open the Manage Parameters window in Power Query. From there, create a new parameter and give it a meaningful name. From there, you just need to specify the type of value, add your value, and click OK. Since I am creating a parameter for a reusable API key, I am just using text and adding in my key.
Creating a Parameter in Power Query
Now that I have the parameter in place, I can use it in an existing query. Since I was looking to easily reuse an API key, I can go and update my queries.
Leveraging my Parameter with Another Query
As an added bonus, I can share my full query without having to remember to hide my API key!
Common Use Cases
While the API key use case above is one of my favorite stories to tell, there are a few other key use cases.
One of the most famous use cases is when you need to create parameters for Incremental Refresh or Hybrid Tables. You need to create a RangeStart and RangeEnd parameters to setup the filtering on the report.
Another great use case is when you need to quickly change values to filter a report. In a past life, I worked for a company that had APIs for external reporting. Since customers always wanted similar reports, I created them with parameters so I could quickly update the API key and customer id. When a new customer wanted a custom report, I just updated the parameters to get their data in, made some adjustments, and sent the report out.
Tips and Tricks
Tip #1 – If you save your Power BI Desktop file (PBIX) as a template (PBIT), you will be prompted to enter the parameters when you use it. This helps save you a few clicks.
Tip #2 – All of your parameters can easily be updated in the Power BI Service without having to open the desktop file. Go to your data source settings and you will find the parameters section. This allows you to update your parameters like API keys with minimal effort.
Tip #3 – If you are using a deployment pipeline, you can easily swap between your Dev-Test-Prod databases. Just set parameters for your server and database fields in the advanced editor window and update them in the Power BI Service.
Function Parameters
Not as common of a use case, but functional parameters allows you to reuse a query quickly. I go into great detail on why to use functions with my article on how to use REST APIs in Power BI, but in short, they allow us to scale a base query rapidly.
How to Leverage It
One of my favorite endpoints pulls stock information by ticker symbol. I could build multiple queries and append them, but a function makes it easier. I just need to add a little code at the top to convert my base query to accept a parameter.
Parameters in a Power Query Function
I specified the parameter that I wanted to gather and then apply it in the query. When I go to use the function, I will be prompted to specify the parameter so the query works properly.
Common Use Cases
I frequently use function parameters with APIs. Because some API endpoints require dynamic values, you will need to iterate with a function like I did with the stock symbols.
Another common use case for me is building common elements I might use in a report. If I am running into queries that are using paging , I can grab a function and invoke it to build out my base. Another common use case is to build out a date table when one is not available in my data source. Of course these are things we want to do as far upstream as possible, but if you are not in control of your database, you might need to create your own.
Tips and Tricks
Tip #1 – Invoking a function as a column is an easy way to specify your parameters with existing column values. The function will run on each row of your query using the values in the columns identified. It is a quick and easy way to apply a function!
Tip #2 – You can edit a query and add a function by hand. Just make sure you specify your parameter in the function or it will not work. I use this all the time for token based authentication with APIs.
Tip #3 – If you are using a few different API endpoints, you can create a base function to handle the majority of your query. Just add a parameter to the function to specify the endpoint. From there, you can add your endpoint or table name in the parameter field and invoke the function which speeds up your connection time.
What If Parameters
What if parameters are different than the other two parameters we discussed. Power Query and function parameters really help us with querying data efficiently. What if parameters are all about what you could do with your data.
How to Leverage It
We often find what if parameters when you want to run some simulated scenarios. I recently did a deep dive on what if parameters, but at a high level, we have two components.
The first component is the creating the parameter itself. We will go to the modeling ribbon and select new parameter. A new window will open and I can specify key components of my parameter which include the name, data type, minimum, maximum, increment value, and default. Once I click OK, a new slicer will appear.
But that was the easy part. We now have a new measure that is <Parameter Name> Value. This brings back the value you selected in your parameter. We now can use this measure in a measure to get our value. You will need to use a little DAX, but it is worth the extra effort.
Common Use Cases
The most common use case is to simulate revenue increases. I add the parameter to a formula that takes their current revenue and shows the change. Everyone is trying to make more money, so it makes sense.
One of my favorite use cases is to help filter data. I built a report that compared property assessments in my town. I used a what if parameter to help filter 3,500 properties to only a handful. Using key values such as year built, livable square footage, and lot size, the parameters found similar houses to the one I selected. Instead of having to fool around with filters, I could just quickly adjust my report parameters instead.
Tips and Tricks
Tip #1 – Always have the parameter make the slicer when you can. You can hide it in the selection panel if you don’t want to use it, but the slider is so nice. It makes the mobile experience even better!
Tip #2 – If you are using a number of parameters, don’t be afraid to create a page for your parameters. You can just sync the slicers to other report pages. This prevents clutter and speeds up load time for a page sine there are less objects in the view.
Tip #3 – You can create your own what if parameters with a custom table. This is nice when you want to use a non-numeric parameter. This is a common practice for filtering reports on things like top/bottom views.
Parameters on Parameters
You might have gotten to this point and said “Wow – I had no clue that this term is so widely used!”. I think it is important to understand the different kinds of parameters because they really can make an impact on your reports. Some of my reports maybe only use one of these while others use all three. The key is understanding how to properly use them.
Have you used any of these parameters before? Do you find them helpful in your report design and data models? If so, tell me in the comments below!
Bookmarks are one of those tools that I never really understood on day one. I just kept ignoring them thinking they were not that important. What a mistake!
Bookmarks serve multiple purposes in Power BI Reports. Regardless of your skill level or design experience, they can help any creator. The trick is understanding how they work so you can leverage them.
The Basics of Bookmarks
Sometimes the most basic solutions are some of the best. Bookmarks personify that point. The easiest way to showcase the basics of bookmarks is to save a view you like to use.
When digging into a report, I frequently apply different filters and slicers to find insights. I often find something interesting and will want to reference it later. Before bookmarks, I would have to take some notes about how to reproduce what I found. Well, I should take notes but often forget. Sound familiar? Instead of taking all of those notes, just apply a bookmark!
Creating Bookmarks
Last year, I created a property assessment report for my town. I built the tool to check the assessment value of houses in town and compare them. The reason? My property taxes are directly tied to the assessed value of my property. Like most people, I checked out other properties in town.
When I found one that was interesting, the easiest thing for me to do was bookmark what I found. To get started with creating a bookmark, I go to the view ribbon and select the bookmarks panel. Once that is open, I can click the add button to save my view.
Adding a Bookmark to Power BI
With my bookmark created, I can now quickly click on it to restore my view. Mess with any of the settings and then click on the bookmark on the right to restore the saved view.
Updating a Bookmark
Sometimes you want to make an adjustment to a bookmark. Maybe you made a mistake or need to adjust your filters. Instead of deleting or creating a new bookmark, you can simply update it.
I can click the three dots to the right of the bookmark name to pull up a menu. There are a lot of settings in here, but we just need to focus on the update option for now. Just simply select update and your bookmark will be adjusted.
We also can take the time to rename our bookmark in the same place. Just select rename and choose one that makes sense. Use whatever works for you – it has to be better than “Bookmark 1”!
Build a Panel of References
One of my favorite things to use bookmarks for is to build out a list of views. When I am trying to create a seamless presentation, I build out my views and just start clicking through them. Before bookmarks, I could take screenshots and put them in a PowerPoint deck, but I lost the interactivity of the report!
To prepare for my presentation, I build out all of my bookmarks. Once they are created, I can drag and drop them into the right order. When I am ready to present, I open the report and show the bookmark panel. I can step through my presentation and provide quality reporting that contains an interactive component.
Extending Bookmarks for Flexibility
Another common use for bookmarks is to provide a little flexibility with your report canvas. They say you cannot make everyone happy. However, sometimes you can using a few bookmarks.
A common scenario I run into is picky report consumers who have specific report visuals they prefer. Some users want to see charts while others want to see tables. You could create a new report page with a different visualization, but that is a lot to maintain for a single visual. Keep it clean with a few bookmarks.
To get started, I will create my two visuals on the report canvas. It will look cluttered for now, but we will fix that shortly.
Adding a Matrix or Table Visual to the Report Canvas
The next thing I am going to do is open both the bookmarks and selection panels. I am going to use the selection panel to hide the table of values from the report canvas. Once it is hidden, I will create a bookmark and label it as “Chart”.
Hiding the Matrix Visual and Creating the Chart Bookmark
Then I will do the same thing to show the matrix visual instead of the chart.
Hiding the Chart Visual and Creating the Matrix Bookmark
Now I have these bookmarks available in the panel on the right and can use it as desired. Regardless of your consumer’s preference, they can pick the right visual for their needs. A simple and seamless solution!
Making Bookmarks Accessible
Now I already know what you are thinking – this is a great solution, but my users won’t know to use the bookmarks panel! I totally agree with you! Until recently, the only option you had was to add buttons and wire up your bookmarks. But with a recent update to Power BI, we can use the Bookmark Navigator to simplify that process.
Before we add the navigator, a best practice is to group our two bookmarks together. This will allow us to flip between our visuals without interfering with future bookmarks. To group our bookmarks, use control click to select our bookmarks, right click on them, and select group. We can then rename the group to something that makes more sense. I chose “Revenue Visuals”.
With that in place, we can go to insert and click on the button drop down. I can go down to Navigator and select Bookmark Navigator. It will then place the navigator on the canvas and add my bookmarks.
Adding the Bookmark Navigator to my Report
We are almost finished. If you remember what I mentioned earlier about grouping your bookmarks, you need to assign the group to the navigator in the format panel.
Set your Bookmark Group for the Navigator
The best part about this solution is that as you add more bookmarks to the group, you can easily add them to the visual. Just create a bookmark and add it to the group!
Adding a New Bookmark to the Navigator
It is true – you cannot make everyone happy. But this might make a few extra people happy with a minimal amount of effort!
Anything Else About Bookmarks?
For report pages that do not have a lot going on, you might be fine with this setup. However, if you try to add a lot of visuals, you might run into performance issues. Instead of updating all visuals, consider changing the bookmark to update the selected visuals instead. Just use your control click on the selection pane to pick the visuals you want to show or hide and update the bookmark accordingly.
I will say, you will probably get your bookmarks wrong the first time. Even as experienced as I am, I occasionally need to keep updating my bookmarks until they are right. You might be frustrated, but don’t give up! I promise you it is worth it in the end!
Have you used bookmarks before? If so, how did you use them? Any favorite use cases? If so, tell me in the comments below!
We all want to hone our skills, but sometimes struggle to find good sample data sets to try out new ideas. Sometimes it is about specific data structure or maybe you want to show off an idea but cannot use production data. A lot of what I share comes from real scenarios I have encountered. To share these tips, I have had to use different data sources over the years.
This week, I want to share some data sets that I find fun and helpful for trying ideas out in Power BI. Some will be easier to use than others. You might even need to have a SQL Server to make them work. Regardless, you should be able to find something you can use.
The Most Basic of All Sample Data Sets
If you are brand new to Power BI, the Contoso Financial Sample workbook is a great place to start. It is a free and easy to use data set for beginners. While it does not a great resource for data modeling, it does serve as a quick and easy model to learn the basics of Power BI.
When I was a Power BI trainer, I liked using this data set for basic DAX calculations as some of the key measures such as cost of goods sold (COGS) where included in the model. I can perform some basic calculations which result in net profit.
Check out the Contoso Financial Sample data set here.
Learn How to Find Insights
Another one of my favorite sample data sets that is easy to use is the Pima Indians Diabetes Database from Kaggle.com. Like some of you, I cringe typing out the name of this data set. If published more recently, it would have likely been given a more culturally sensitive name. However, I use this data set for demonstrating the key influencers visual. I have also used it for predictive modeling with Azure Machine Learning, but that is for another day.
This data set was assembled by the National Institute of Diabetes and Digestive and Kidney Diseases. The purpose of the data set was to perform predictive modeling on diabetes in the Pima community. Identified as a high risk population for Type 2 Diabetes, this data represents the Pima community through years of research. Kaggle provides this data set for free. You just need to sign up for an account to access it.
Kaggle is a great resource for other data sets. There are so many to choose from, it is hard to just pick one. However, you are welcome to peruse their catalogue as you might find something interesting. With a little searching, you will find a data set which you can use to build a report on Settlers of Catan!
Check out the Pima Indians Diabetes data set here.
Simplest of SQL Sample Data Sets
Adventure Works is likely the world’s best know SQL database.. A common data set used for training, it is easy to implement. Experience with SQL Server Management Studio will serve you well as you implement this data set. Microsoft provides clear instructions on restoring the database but I find a little extra know how helps. It is wise to make friends with a database administrator if you don’t have one. Offer to buy them a drink or two at happy hour for their help and you will probably make a new friend out of the experience.
Fans of The Office rejoice! TDMitch created a Dunder Mifflin sales data set from the Northwind Traders data base by Microsoft. Just like Adventure Works, this is a SQL data set. Implementing this data set requires additional effort compared to the Adventure Works database. You must follow instructions and run a few SQL scripts to finalize the setup of this data set.
I recommend this data set for someone who is trying to make something that connects with end users. I also recommend this data set for people who are expanding their transact SQL knowledge.
REST APIs are great resources for 3rd party data. They work well but you might find frustration with implementing them. I have used this data set before with my series on the Basics of REST APIs in Power BI. While each API endpoint is unique, you can capture the basics using the Yahoo Finance API.
Offered for free up to 100 calls per day, it is an effortless way to learn the basics with no costs. If you are really into stocks, you might even consider purchasing a paid subscription. Spend some time digging through the endpoints and become comfortable with how you can use APIs with Power BI.
You can review the Yahoo Finance API documentation here.
Big Data Sample Data Sets
Sometimes you want to throw a lot of data to test out a solution. The New York City Taxi data set is a massive trove of data that is free for use. Available as CSVs or APIs, you can choose how you want to access the data. I used it to benchmark refresh speeds between various Azure data sources such as blob, table, data lake, and Azure SQL storage solutions.
The Taxi and Limousine Commission provides quality documentation around the data set. It even provides clear descriptions in the data dictionary, maps of taxi zones, and dimension tables. It even explains the difference between yellow taxi, green taxi, and for hire car services.
Check out the NYC Taxi and Limousine Commission data mart here.
Did Not Find Something To Fit Your Needs?
No fear about that! There are tons of free data sources out there for you to use. My favorite place to go is to data.gov and check out different data sets available from the US Federal Government. You can also search for open data from many states and cities. You might even be able to use it for some of your solutions.
Google also has a data set search that will help you find some samples. Search for different topics such as commodities or labor statistics and see what comes back. My only caution is that not every result will be free. However, if you are looking for something specific, this search will help you find what you data you need.
How about you? What are some of your favorite sample data sets? If you have a good one or used one of these, tell me in the comments below!
In my article last week, I explored how to compare a goal built off last year’s revenue compared to this year’s actuals. Some of you might be thinking that system worked, but it felt cluttered. I would agree with you. I like using cluster charts in the right scenario, but I think we can do better. A couple of weeks ago, I did an article on why I love using conditional formatting to highlight important values. You also might remember that it was focused on using the table or matrix visual.
A little known fact is that you can leverage conditional formatting in other visuals. Imagine using conditional formatting to quickly identify which months we hit or missed our goal. I thought it would be great to dig into some options to really make your column charts pop!
Using Some Conditional Formatting Magic
A few weeks ago, I wrote an article on how to do perform field based conditional formatting. The same principle applies to building out our formatting in our charts. I start by building a measure that compares current year revenue to our goal.
The formatting will show green if revenue exceeds our goal. We then identify any months that reached 90% of their goal in yellow to highlight that they were close, but did not quite make it. Then any months where we missed our goal, it now shows in red.
Now we need to apply the formatting to our chart. After selecting the visual, we need to go to the visual properties pane. Then you need to expand the columns section and click on the “fx” button. From there, just select the field value formatting style and select the CY Revenue Color measure we just created.
Applying our measure for conditional formatting.
The result is a chart that looks like this:
Field based conditional formatting on a column chart
It was a few extra steps, but it makes it clear we started the year off strong, but have some work to do to get back on track. Amazing what a little color can do!
This Is Great, But…..
I know what you are about to say – “I love the color, but it is hard to see how this compares to the actual goal. Can we do something about it?” Of course we can!
When traditional conditional formatting lets off, we can build our own with a series of measures. Using a stacked column chart and a few measures, we can show our revenue versus the goal to make it easier to see. We need to build four measures to make it easy to track against our goals: Revenue up to our goal, revenue that exceeds our goal, revenue short of our goal, and revenue left to meet our goal. Yes, it seems complicated, but just wait until you see the final product.
Our first measure will calculate the CY Revenue that is up to our designated goal. If we exceed the goal, we will just return the CY Goal instead and represent the remainder of the revenue in another measure.
Next, we want to show anything that is over our goal in another measure. Using a similar formula, we will calculate the difference of CY Revenue and the CY Goal. Note there is no else statement as we are only interested in revenue over our goal.
We don’t want our chart to show missed revenue for months that have not happened yet. We also don’t want to show missed revenue for our current month but rather as revenue to go. By using EOMONTH(), I am able to take the end of the month that we are in today and subtract a month so it will only summarize data before my current month.
Now doing something similar, I can calculate the revenue that still has to happen before the end of my current month. We will essentially do the same measure as above but looking forward.
CY Revenue to Go =
IF(MAX(SalesAggregated[Month]) > EOMONTH(TODAY(), -1),
[CY Goal] - [CY Revenue])
And with a little color assigned in the, we can make our chart pop!
Homemade conditional formatting using some measures and custom colors
Which Method When?
Both methods work really well for providing conditional formatting. But one obviously takes a lot more effort to create. And beyond that, I need to make sure my DAX is clean and doesn’t slow down my report.
Why would I go down this route? I find that sometimes you have a stakeholder who really likes to get into the minutiae of the data and wants to be able to accurately see their plan versus actuals. This might have been a little more complicated than required, but I wanted to show you how granular you can get.
From my experience, most people do not want to get this granular with their reports. But in case you run into someone who really wants some additional detail, you now know how to make it happen.
Have you run into something like this before? Have you been asked to add some formatting to your column charts? Are you going to start doing it? Tell me in the comments below!
What If? A common question we ask all the time. What if we increased our revenue 20% from last year? What if we increased our margins by 2%? We ask these questions frequently, but do we do anything to solidify next steps to figure these things out?
Power BI makes it easy for us to run these scenarios in a dynamic format with What If Parameters. However, I find most people avoid using them because they need to do some DAX to integrate them. Today, I want to demystify this and help you build out your own scenarios. Our scenario will allow us to create a revenue goal that we can adjust based upon the previous year.
Setting Up Our Revenue Measures
To get started, it is important to build a measure to calculate both our current year and previous year revenue. This will provide a base to perform our comparisons.
I start with a base calculation that will summarize our sales data for the current year:
CY Revenue =
SUM(SalesAggregated[Revenue])
Which results in this column chart:
Current Year Revenue in a Column Chart
Next, we will leverage our current year revenue to calculate the past year’s revenue:
This results in the clustered column chart we created in my article last week:
The result of our measures allows us to see the previous year’s revenue for all of 2021, but only the current year revenue for Q1 of 2022. Our goal is to next add a What If Parameter so we can create our 2022 goals.
How Does a What If Parameter Work?
What If Parameters are built out in the Power BI Report. In fact, when you create a parameter, it does a lot of the hard work for you. Let’s start by creating a parameter and see what happens in the background.
Creating a What If Parameter
To get started, go to the modeling tab and select new parameter. A new window will appear which will require some input. First thing we will want to do is set our parameter name. I am calling mine “Revenue Increase %”.
Next, we need to configure the parameter. Because we are working with percentages, we need to change the data type to decimal number. Then, we need to set our minimum and maximum values. Because we do not want to lose revenue, our minimum will be zero. Our maximum will be 2 which will allow a 200% increase of revenue.
Lastly, we need to set the incremental value. We are going to specify .05 which will allow 5% increments. We will also set our default our value at zero. You will also notice that there is a box checked to add a slicer to the report canvas. We will leave that checked and click ok.
Setting Up a What If Parameter in Power BI
The most obvious item created is the slicer that appears on the canvas. But there is so much more to this simple process that happens in the background.
What Else Is Created With a What If Parameter?
To answer that question, let’s head over to the table view of the report. A new calculated table shows up on the left hand side that matches the name of our parameter. When we select it, you can see there is only one column which lists our percentages from 0 to 200 in decimal format.
But how do we leverage these numbers? The slicer will limit which value is being used, but how do I build a relationship to my revenue? While there is only one column on the table, there is a measure that was created as well:
The SELECTEDVALUE() function looks for the value you have selected with the slicer and returns a single value. In this scenario, it is looking for a single percentage value in that column that was created. If it finds more than one value, it returns a zero instead.
Is There Any Other Setup Required Before We Use It?
At this point, everything is in place for a usable What If Parameter. However, I am always a fan of cosmetics and making things look a little cleaner. Everything is currently in a decimal format, but we are looking at everything in percentages. As a result, we will want to select both the column and the measure created and make sure they are formatted as a percentage with no values after the decimal place.
Formatting What If Parameters for Percentages
We can also consider adding a card to help highlight the percentage we want to use over last years numbers so it is easy to find. We can use that measure created to make it easier to see on the report canvas. This is helpful if you are syncing the slicer across multiple report pages.
Our What If Parameter on the Report Canvas
It is important to understand what is being built in the background because it lays the groundwork for using the What If Parameter in our report.
Applying a What If Parameter
Now that our parameter is set, we need to apply it to our report. The first step is creating a new measure that will calculate our goal.
If you remember, our goal was to calculate our current year goal as a percentage over our previous year revenue. To accomplish this, we used our previous year measure and multiplied it against the measure we created from our What If Parameter and added one. Why add one? Because we want to grow our revenue, so we add one to set the base as 100% and can grow it from there.
With our new measure created, we can apply it to our column chart:
Previous Year Revenue, Current Year Revenue, and Current Year Goal in a Clustered Column Chart
Now that everything is in place, you can adjust the goal at the beginning of the year to set the goal. At the end of the year, you can easily adjust the goal to align with what happened over the entire year to determine what your goal is for next year.
Next Steps
We started with a single What If Parameter, but if you have multiple you want to adjust, you can create more of them. In the event that you have a complicated list of values that has a lot of rules, you can consider some other options as well. While we did create everything with a few clicks, you have the ability to write all of the DAX on your own. You can build out a table, create the slicer, and a measure to leverage it. The reality is you can create flexible parameters to meet any scenario!
How about you? Do you have any reports that could benefit from a little flexibility? Have you used What If Parameters before? If so, tell me in the comments below!
I cannot begin to express how often I see bad examples of data visualization. Sometimes it is unintentional but it can have far reaching implications. Not following best practices and visualization standards often misleads report consumers and eventually bad decisions.
Even worse, report creators often manipulate these visuals to get the data to support their narrative. I am all for sharing a narrative with data but I draw the line at manipulation. If you have to twist and manipulate your visuals to tell your story, it is probably time to get a new story.
I am passionate about this topic because I watch people manipulated by these visuals all of the time. Sales presentations, all hands meetings at work, poll results, and even the news media commit these violations. My goal for this article is two fold. The first is to help you understand the why of these best practices for your own reports. The second is to help you identify these violations in the wild and help call them out.
My articles usually focus on Power BI. However, this article applies to any visualization tool you will encounter.
The Importance of Best Practices and Visualization Standards
You might be asking to your self “why is Dominick so passionate about this?” To me, the loss is considerable if you are not careful. Here are some common scenarios where not following best practices can get you into trouble.
Financial Costs
This is the first scenario that often comes to mind when it comes to bad visualizations. We use reports and visualizations to make data driven decisions.
Let’s pretend you own a store that sells widgets. You take a look at a chart that shows your sales doubling month over month. As a shop owner, you might double your order for next month because if the trend continues you want to be prepared. It totally makes sense! You double up your orders (or even triple them) to meet demand.
Column Chart Showing Revenue Doubling Each Month
However, I left something out of this chart. There was no scale and my axis did not start at zero. In fact, I never actually doubled my sales. I only had a 10% increase of sales month over month.
Column Chart Showing the Same Data Above with Proper Scale and Labels
Now you might be thinking that this really is not a big deal as widgets do not have an expiration date. But the implications of this could be considerable as you took cash in hand and put it into inventory. You still have an assets on hand with the widgets, but you will need to sell them to make sure you have cash on hand. This could put you in a bind as an owner as you could potentially become “cash poor” which results in trouble paying bills, making payroll, and even paying yourself.
Someone Could Get Fired
Have you ever created a report that could get someone fired? I have!
One of the most common reports I see are around service level agreements – also known as SLAs. SLAs determine effectiveness of a process or service. Contracts include these metrics that help determine success of services.
Remember when pizza shops had the “30 minutes or its free” guarantee? It went away before I was old enough to buy my own pizza. Likely because someone did an analysis of their metrics and found they were not delivering enough pizzas on time.
If you managed a pizza shop, you might have had an SLA of delivering pizzas in under 30 minutes 97% of the time. That means out of 100 orders, three of them could be free because they did not make it on time. Your district or regional manager likely has a report showing your performance. What if they showed a report that showed a trend where your on time delivery metric dropped significantly. You might be in danger of losing your job for not performing as expected.
But if the chart is only moving from 99% of the time to 98%, it is still above the SLA and overall not too bad. The point here is that visualizations not following best practices can imply someone is not performing well in their job. This leads to someone getting disciplined even if they are performing well in their job.
Your Future Ambitions
People are often looking for ways to grow their career. They put together statistics and figures to show how great they are and why they deserve a raise or promotion. They might also provide some of the same information to help those above them move up the ladder as well. But what do you think the implications are if your visualizations used for this purpose are found to not use best practices?
I see it as a breech of trust. While it may seem insignificant at the moment, the impact could be long term in your career. Every report created could be scrutinized and called into question. It seems crazy but the potential is there. If a decision made from the report costs your organization a considerable amount, you might even lose your job!
This is why I believe it is important to consider best practices whenever creating reports. Leaders must take the time to make sure their teams understand these practices and live them for the benefit of their team.
What Should I Know About Best Practices?
There are 4 best practices that you should be concerned with when you create your reports. Follow these and you will be well on your way to building quality reports.
Best Practices to Follow #1 – Always Start at Zero
This is a non-negotiable standard when it comes to bar and column charts. They must always start at zero to avoid misleading consumers. This is the most common infraction that I see when it comes to data visualizations.
As discussed above, I can show amplified trends by changing the axis to start at a higher value. This also causes confusion and frustration for report consumers.
Column Chart That Starts at Zero
Best Practices to Follow #2 – Label Your Axis
The next most common ignored best practice is labeling your axis. We see the categorical axis getting a label (category, time, etc.) but he value axis does not get the same love.
To avoid confusion, you will want to label your value axis to match the measures in your chart. There is some flexibility with this as you can change the units to make it easier to read. For example, you can set the units to millions to compress the chart and make it easier to read. While you might be distorting the view some, the labels will clearly define the scale and make it easier to understand.
Labeling Your Categorical Axis and Value Axis
I also recommend using some lines to highlight your axis across the chart. I worked with an organization a few years ago that hated those lines. It was forbidden to add them to any chart in a presentation. While I strongly disagreed with this approach, they used data labels which helped keep things in context when viewing a chart.
Best Practices to Follow #3 – Use Data Labels to Highlight Key Values
My friend and colleague Sally Paczosa always used the term “hover to discover”. I stole her line and say it all the time when it comes to reviewing reports in Power BI. If I want to know the actual number, I can hover over a column or bar and see what the value is. It is one of the best features that people forget about when it comes to reporting.
But as much as I love it, I always have to consider my audience for a report. If my consumers are fluent with tools like Power BI, they already know what to do. Without proper training, they might miss that opportunity and never actually do it. In the same vein, I have to consider the venue they will consume the report. They can “hover to discover” all day long at their computer. But what if they are in a town hall meeting looking at a chart being presented? How can they view the actuals?
Data labels allow us to quickly identify key measures on a report and allow us to add context quickly. This prevents the fatigue of trying to match columns to the axis and accelerates understanding.
Using Data Labels on a Column Chart
Best Practices to Follow #4 – Add Lines for Clarity
I like adding lines to help provide better context and clarity to a chart. This is extremely helpful for reports that will be consumed without a talk track behind them. You can highlight common statistical measures such as maximum, minimum, mean, or median. You can even add a constant line if you want to highlight a particular goal. If not used properly, it could clutter your charts. However, properly placed it could make a huge difference.
Adding a Constant Line to Highlight a Goal
Best Practices Apply to Chart Types as Well!
Which is better? A bar chart or column chart? The answer will depend on the context of your data and the intent of your visualization. Here are some thoughts to help you make the right selection
Column Chart vs Bar Chart
People tend to use these terms interchangeably but don’t realize there is a difference between a bar and column chart. In a bar chart, the data is represented horizontally. Column charts represent the data vertically.
Column Chart vs Bar Chart
While it may seem inconsequential to interchangeably use these terms, it is important to really understand the difference to help represent your data.
Column Charts
Column charts can be used for categorical data, but they often shine when it comes to series based data. The natural left to right progression of the canvas allows users to follow a timeline to see trends.
They also shine because the vertical orientation of the values is easier to grasp some statistical measures such as maximum and minimum. It is less cluttered in this orientation and the labels are easier to read.
Bar Charts
Bar charts do an amazing job of showing large amounts of data in a compressed area. If you have long list of category labels, they tend to fit better horizontally. This prevents crowding and confusion.
In addition, they do a great job of allowing users to scroll down for large amounts of data. Naturally, your venue is going to have an impact on this functionality. You cannot just scroll down on a chart if you are in the audience of a town hall meeting. But for reports that are consumable at your desk, it is easy to scroll down and see more information.
Stacked and 100% and Clustered, Oh My!
Now that you have a hint at which direction you want your charts, it is time to pick the right one. If you only have a single series of data, then you could pick a stacked or clustered chart and it would not make a difference. But depending on what you are trying to show, it is important to pick the right chart.
Stacked
Stacked charts show the composite makeup of the whole value. Let’s stay I own a store but recently started selling my products online. I might want to see the breakdown of my revenue from my store versus online. A stacked column chart allows me to quickly identify the makeup of revenue which can help provide context. For example, if I have a month with higher than expected revenue, I might see that online sales were higher than in the past.
Example of a Stacked Column Chart
While this chart style is versatile and useful, it is not always easy to make comparisons. The orientation of the values makes it difficult for one to one comparisons.
100% Stacked
Similar to the stacked chart, 100% stacked charts show data in a similar format. The biggest difference is that the bar or column do not show totals. Instead, it shows the percentage of the composite elements.
Take our store versus online sales question. If you looked at the chart above, you could see the breakdown. But what if I wanted to understand the percentage of my sales that were in my store versus online. I could do some math to figure it out, but it is easier to show in the 100% stacked chart.
Example of a 100% Stacked Bar Chart
This is where the 100% stacked chart shines. It allows us to understand distribution a little easier which gives us a better comparison. We can quickly identify trends of online sales and see if they are increasing, decreasing, or remaining flat over time. The only downside is that you cannot see the overall totals.
Clustered
Clustered charts allow for quick comparisons. Not as precise as the other two charts, it does a good job of allowing you to compare two values side by side. Where most people go wrong is they try to compare categorical data which can get confusing. I like to use it to compare year over year or even month over month data. This allows for a close comparison without a lot of clutter.
Example of a Clustered Column Chart
What If I Am Unsure of Which One to Pick?!
The best part of most tools is that you can quickly change which chart you are using! Try some different ones. Maybe you will need to use a couple of variations to get your data to appear correctly. The goal is to communicate a story. One of my favorite ideas is to show the visualization to someone and have them interpret it for me. Don’t give them any hints – let them tell you. If what they say matches your narrative, then you are likely on the right track!
Next Steps
Now that you have a better understanding of best practices when it comes to bar and column charts, what do you do next? My first recommendation is to go back through your reports and presentations and find out if you were following these best practices. If you weren’t, try to understand why. I find that we tend to take shortcuts to save time when we have a hard deadline coming up. Building in a little extra time in your plan helps ensure nothing gets left out.
What about when we see best practices not being followed? What do we do? I find most people make these mistakes out of ignorance. They might know all of the best practices or how to apply them. I always recommend coaching people and helping them learn. I also find being the best example helps change the behaviors around you. By getting others to follow your lead, you will find others will do the same.
How often do you see these best practices ignored? Do you have any egregious examples? If so, tell me in the comments below!
