It is a pet peeve of mine and it drive me nuts. I hate it when someone tries to fit as many visuals on the canvas as humanly possible. It is cluttered, ugly, and hurts the performance of your report. But how do we get around this issue? The answer is simple! Add a drill through to your report!
I will be honest – I was confused by the initial setup like you might be. The setup is a little intimidating at first, but once you understand how to configure the drill through, it is easy to implement. Like what I said at the end of my article about bookmarks in Power BI, you will probably get things wrong the first time. However, with some guidance, I hope you will be able to implement them with ease!
Preparing Your Report
Generally, this starts with a basic report page with a goal. Right now, I have a Sales Data report page that has a lot of information. However, I want to drill down on a manufacturer and learn more about the products being sold. If you look at our current Sales Data tab, you will note that there is some product information, but not the context I want:
Current state of the Sales Data report
Our first challenge is to create a meaningful drilldown page that shows information by manufacturer. In my scenario, I wanted to see the top products by both revenue and quantity. I also wanted to see product segments and have the ability to filter by category. The result is a drilldown page that looks like this:
Manufacturer drilldown report page
You will note that I did not place a slicer for manufacturer on the canvas. This is because the drill through will handle the filtering for us. We will tackle that next.
Add Drill Through Logic
With our detailed report created, we need to enable the drill through functionality. On the report page, we must add the field we want to use for the drill through. Since we want to look at the manufacturer, we will add that field to our visualization pane under drill through:
Adding the drill through field
One thing you might not have noticed right away is the back arrow that was added to the page in the upper left hand corner. This allows you to return to the original page when you drill down on your data. By default, this is set to just go back to the last page. However, you might want to hard code a page navigation. This will ensure you return to the correct page and is a requirement if you allow drill through on multiple levels:
Configuring the back button added to the report canvas
You will note that with the drill through, there were two additional settings available to us. The first setting is on by default which is to keep all filters. This simply maintains the filtering from the page where the drill through occurred. So if it is filtered on a specific year, it will be maintained.
The cross-report drill through allows you to make this report page available on other reports. When published to the Power BI service, other reports in the workspace can drill through with this page and see the detail. This allows you to reuse this page on multiple reports which is very convenient.
Testing the Drill Through
With everything in place, it is time to test our drill through. Going back to the Sales Data page, we can right click on one of the bars in our quantity by revenue visual. From there, we can see the drill through menu option with our new report page:
Testing our drill through settings
If you have the ability to select your new page, then you did it right! Go ahead and click on it and you will see the new detail page filtered on the manufacturer:
Drill through report page filtered for Van Arsdel
It take a little work to configure, but the results are beautiful. It does a lot to help clean up your canvas and allow you to get more detail on a specific dimension. With a clean look like this, it makes it easier to consume your report with the ability to dig further for details.
Anything Else to Consider?
What if you want to use other fields for the drill through on the same report? You can easily facilitate that by adding them to the drill through field. This will give you the ability to use the same page for multiple dimensions to reduce effort.
In addition, consider the detail of your page when designing it. The ability to reuse it over and over again will reduce a lot of repeated effort. If you leverage the cross report feature, it could make your drill through page an extremely helpful feature to any report you create.
Conclusion
This is one of those unsung heroes of the Power BI Report. It is hidden in plain sight and not always easy to create. But with a little effort, it can really transform your report. It is worth the time as it will take the experience of your reports to the next level!
So, have you used drill throughs on your reports? What are your use cases? Did I miss anything? If so, tell me in the comments below!
Nothing is worse than a cluttered report canvas. This is one of the most common pieces of feedback I provide to customers on a regular basis. The idea that you can pack as much information into a single view is honestly obnoxious and leads to confusion. If readers have a hard time understanding the canvas, they will not understand the message you are communicating with your report. And the experience is worse if you have half of the canvas covered with slicers!
In addition to the confusion, it has the chance to impact your report performance. As you add visualizations to the Power BI canvas, you will hurt the overall performance of your report. Because each visual must be rendered and associated data queried from the model, it will impact the responsiveness when loading. But a little known secret about Power BI is that if you hide the visualization from the canvas, it does not impact the performance.
This is where slicer panels come in. Instead of having a cluttered canvas full of slicers that is slow to load, we can hide these slicers and activate them when needed. This is a small change to your canvas, but it provides a lasting impact. It only takes a few minutes to implement if you know how to do it.
Example of a cluttered report canvas
To resolve this issue, we will simply add a slicer panel to our report canvas. The best part of this is that we will be able to leverage our existing slicers to save us time!
Prepare the Slicer Panel
The first step in preparing the panel is adding a background. Go to the insert ribbon and insert a square shape from the dropdown. Once it appears, resize it and place it on the canvas. Personally, I add the slicer panel on the left side. However, you may choose to have it appear somewhere else on your canvas. My recommendation is to use whatever enhances your report design best is ideal.
Inserting a shape for the slicer panel
Next, we need to add a way to close the panel. Under the insert ribbon, we will expand the button drop down. From here, you can pick the one that works best for you. I personally use the back button, but the reset button seems to work well for others too. You might need to perform some updates to the formatting so it is easily visible on your slicer panel.
Inserting an exit button for your slicer panel
Now that we have our panel in place, it is time to add our slicers.
Loading Slicers into our Panel
In our example, we already have our slicers in place. We are going to use the selection pane, found under the view ribbon. From there, we will re-order the items on our canvas so our panel appears above any visualization we add to our report. We also want to make sure our slicers sit on top of our shape so they are not hidden. Once this has been completed, you can arrange your slicers on the shape.
Updating our canvas order and arranging our slicers
Next, as a best practice, we will want to rename our shape and our button. The traditional way to perform this task is to update the title under the general settings of the format visualization pane. However, there is an easier way to perform this action! Instead, just double click on the object on the selection pane and you can update it right there.
Renaming objects on the Power BI canvas
You will note that I have also grouped these objects together by selecting them, right-clicking, and selecting group. I renamed the group the same way I did with the other objects. This is not required, but makes life a lot easier for the next step.
Show and Hide your Slicer Panel
Now that we have our panel in place, it is time to hide it. To accomplish this task, we will use bookmarks. I have already written extensively about how to leverage bookmarks in a previous post. If you are not sure how to leverage them, please use that link to learn more. In this article, I am just going to show you how I quickly configure them so the panel will appear and disappear when desired.
I have already added two bookmarks – one to open and one to close the slicer panel. I will simply configure my bookmark to only update the selected visuals I have clicked on and update the slicer. In the image below, I am opening the slicer panel, so it is visible. However, I will just simply hide the visuals for closing the panel.
Updating bookmarks to open and close the slicer panel
Now that this is in place, I just need to update the actions on my buttons. To apply the close slicer panel action, I will select by button in the slicer panel, go to the format pane and expand action. Once there, I will set the type of action to bookmark and select my Close Slicer Panel bookmark. I will do the same to the open slicer button inside of my report footer.
Applying the close slicer panel action to the close slicer panel button
Now that you have this in place, you can easily open and close your slicer panel. To test this in Power BI desktop, you will need to hold down the control key to activate the action. However, that will not be required when you publish the report to the Power BI Service.
Opening and closing the slicer panel
Just like that, we have provided more space on our report canvas and made it easier to view!
Anything else to consider?
If you remember, I put my slicers in a group with the panel objects. While not required, it is a best practice. The reason for this is that you will at some point need to add a new slicer to your report. To make it easier on yourself, the group allows you to update the objects without having to update your bookmarks.
When I am in a hurry, I feel like bookmarks never update the way they are supposed to. The result is in a rushed experience that never works out. However, because I used groups, I can simply drag my slicer into the group and not have to make any adjustments to the bookmark. It is an extra step in the process, but it will save you considerable time and headaches in the future!
Conclusion
This is a small update for a report, but it really makes an impact. I know it takes extra time, but I promise you that it is worth the effort to preserve your canvas. This is especially helpful if you have reports using more than five slicers for a page.
Have you used a slicer panel before? Do you find them helpful? If so, 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!
Power BI Reports serve several purposes, but they often consumed by larger audiences. As a result, we often strive to provide a quality viewing experience for our report consumers. Most people jump right in with the creation of their reports and neglect how the report is going to look and feel. This is common as we want to get to the good stuff as quickly as we can. But as they say, an ounce of prevention is worth a pound of cure. Taking some time to setup a theme file can really speed up the development process.
Why Are Themes Important?
So before you say that his is a lot of work to just make reports look pretty, it is really more than that. The look and feel of your reports are critical – especially in a self service environment. The look and feel of a report can be a subtle signal to the consumer of who created the report and the reliability of the data.
The Reliability of Poorly Branded Reports
I often work with customers who have created reports that are consumed by large audiences that use color palettes and fonts that do not align with the company branding. This is common with the first several reports that are created. Eventually, someone gets ahold of a branding guide and starts using elements in their reports. Updating the bars, colors, or lines to match the corporate branding helps add legitimacy to the report. Little by little, more corporate branding is added until the theme is fleshed out.
But what happened to those original reports? The ones that used no theme or some of the theme? They often remain neglected. New employees receive links to these reports and often ask themselves “Is this information accurate?” Eventually they will trust the data, but they spend weeks asking if it is correct.
It seems silly, but the branding can instill confidence in your reports.
Managing Branding Manually Hurts in the Long Run
Another common challenge I have identified is inconsistency between visualizations. Actually, it is difficult to even do within a single visualization. When you look at a simple column chart, you would think it is really easy to keep your fonts consistent. Here is a breakdown of where you can make changes to the fonts for a column chart:
On the X Axis
Value labels
X Axis titles
On the Y Axis
Value labels
Y Axis title
Legend
Small Multiple Grids
Column data labels (can be different for each series)
Visual title
Tooltip text
Add it up and you have a total of nine places to update the font so it is consistent! That is unbelievable! And if I have to do this for multiple visualizations, this can become a lot to manage. And without a consistent theme, I have to keep making these changes. So how do I prevent this from happening to me in the future?
Make Themes Work for You
Like I mentioned before, a little planning can go a long way. Here are my steps to help simplify the process.
Step 1 – Find the brand identity documentation for Your organization
Hands down this is sometimes the hardest part of the process. If you work at a company that has a strong brand identity, this should not be difficult. There is likely a brand identity guide or a PowerPoint template that has all of the details you need such as the color HEX or RGB codes and fonts located in the margins of the slides.
If you cannot find a brand identity guide or PowerPoint that has that information, I always recommend becoming friends with someone in your marketing department. They have this information readily available for their projects. It might cost you a few drinks at Happy Hour on Friday night, but it is a lot easier than having to get this information manually.
If you are not as lucky, you can still get this information. It might be a little more difficult, but you can find it embedded inside other resources. You can usually find a PowerPoint template that has the basic colors and fonts identified. If you select a text box in a presentation, you will likely find the font used for the majority of the presentation. You can also click on the font colors to see if they are the ones your organization uses. If they are there, select a color one at a time and then click on more colors. Doing so will bring up the RGB and HEX color codes for the selected color. I like to copy the HEX colors as they are easier and paste them into an Excel spreadsheet with the color name so I can quickly reference them.
Still cannot find your colors? Not to worry. Find a digital copy of your company’s logo and add it to a blank PowerPoint presentation. From there, use the eyedropper function under the font color dropdown and use it to extract the key colors to your organization’s logo. Then follow the steps above to extract the color codes.
Step 2 – Build out your Power BI Theme
With the RGB or HEX color codes in hand, it is time to update your Power BI theme. Go to the View tab in Power BI desktop and click on the drop down to the right of the themes.
Accessing the Power BI Theme customizer
From here, you can start to build out your theme. Start by adding a name for your theme. From there, update the color palette. Simply add the RGB or HEX codes you identified into each of the positions. Keep in mind that the order of your colors will match the order they are selected for legends. If you have a particular combination of 2-3 colors you like to use a lot, make sure they sit in the first three positions so they can easily be leveraged.
Setting up the color palette for your Power BI Theme
Next, you will want to address the text in the report. By default, Power BI is set to use a blend of Segoe UI and DIN fonts. The list of fonts is limited to ensure they stay web safe and visible in the Power BI service. Your first choice might not be in the list, but most organizations have a secondary font that is for such an application. You can also set default sizes and colors for generic text, titles, cards/KPIs, and tab headers.
Customizing the text for your Power BI Theme
Step 3 – Tweak your Power BI Theme until it is set
Once you have the basics in place, you can start making other adjustments to your theme. Other items you might choose to customize include visual backgrounds, borders, report backgrounds, and other adjustments. You have a lot of flexibility to make adjustments that meet the needs of your reports, so play around with them. Whatever you do, try as hard as you can to avoid the formatting pane for your visuals.
Understand that this process can take a little time, so do not be afraid to spend more time than expected. The more customizations are set in the theme, the less customizations you will have to apply to visualizations when you build your reports!
Step 4 – Export your Power BI Theme
When you are comfortable with the theme you have developed, it is time to export your theme so it can be used with other reports.
In Power BI Desktop, go back to the View tab and click on the dropdown next to themes. Select Save Current Theme and save it to your OneDrive. This way it will be automatically backed up in the event of a computer emergency.
Step 5 – Deploy your new Power BI Theme
This is where things can get a little hairy. You have a few options for deploying your theme to others within your organization.
1). You can distribute the Power BI Theme file you created. Users can import the theme every time they want to use it when building a report.
2). You can create a Power BI Report using the theme. Leave it blank and save it as a Power BI Template (*.PBIT) file and distribute it.
Both solutions work, but they feel a little clunky. For me, I have found most of the report creators are quite savvy with theme files, so it is not a big deal. However, if you are interested in building a strong citizen developer community, this could prove to be challenging.
Best practices/considerations for building Power BI Themes.
When you build out your theme, keep these best practices and considerations in mind to help drive the best experience for your report consumers.
Check your theme against multiple visualizations
The temptation is always there – if you only use bar and column charts you only check those visuals. When building out your theme, make sure you try all of the different types of visualizations to make sure your theme works across all available visualizations.
Consider a style guide for reports
If you followed the first best practice, this should be an easy one. Take the report file you used to test the theme and create a sample report. From here, add in key information such as how to label data, types of charts, and other organizational standards that have been set forth by your team. This will help content creators have a roadmap for building quality reports.
Consider color contrast to make reports accessible to all
This might be a bigger concern if you do not have an existing style guide from marketing, but is always an important consideration. Content is difficult to consume if creators use poor color selections. Even worse, it causes strain for users with visual disabilities who have trouble viewing the content. Use a tool like WebAIM’s Contrast Checker to ensure the best experience for all report consumers.
Conclusion
It is a lot of front end work, but I promise you that you will love how easy it is to deploy reports when you have a custom theme. Take the time to build it out right and you will never have to mess with updating individual visuals ever again.
Are you using themes already? Do you have some tips/tricks on how to use them? How are you distributing themes? Leave a comment below and let me know!
