Tip #1: Conditional Formatting Charts
Conditional Formatting is a great way to draw attention to key areas of a visualization based on some criterion, like increasing or decreasing sales.
Conditional Formatting is easily applied to visualizations like tables by selecting the Conditional Formatting section of the Formatting controls.
Unfortunately, Chart visualizations do not have such a formatting section. But that doesn’t mean we can’t use the feature; we just need to be a bit more creative in our approach.
Take the following column chart:
We want to display the positive percentages in GREEN while displaying the negative percentages in RED.
- Select the column chart visualization in the report.
- Click the Formatting icon (paint roller icon).
- Select the Data Colors
- Click the Fx button next to the Default Color
- Set the Format Style to Rules.
- Set the “What field should we base this on?” to “Sales YoY %”.
- Set the rule to the following settings, using red as the color.
This will give us the red bars for negative values.
For the positive, green bars, in the Conditional Formatting dialog box:
- Click the New Rule
- Set the rule to the following settings, using green as the color.
We now have our red/green color scheme based on values.
Changing the data to focus on a different year shows a change in the data but maintains the red/green color pattern.
Featured Course
Fast Track to Power BI
Tip #2: Align Objects
Nothing can spoil a report more (other than incorrect results) than a haphazard arrangement of visualizations.
Aligning and spacing visualization is such a quick and easy process, yet many designers skip this step, producing less than stellar results.
Dragging a visualization around the page will reveal the quick alignment lines. These red, dashed lines are a great way to align one visualization with another.
These work well when you only have a few items to align, but what if you have many items?
To align multiple items in bulk, select the desired items (either by dragging a selection box around the items or using the CTRL-Click technique to select the items one at a time.)
Next, select Format (tab) -> Arrange (group) -> Align.
From here, you can select six different alignment strategies. For example, we’ll use Align Top.
We can even set the spacing between visualizations to be equal by using the Distribute controls.
We’ll use Distribute Horizontally for our example.
We can also align visualization along a left edge and space vertically.
Featured Course
Master Excel Power Pivot & DAX (Beginner to Pro)
Tip #3: Organizing Measures
Creating Measures is one of the more challenging to learn but greatest reward-giving features in Power BI.
One of the first things users of Measures learn is that you can store them in any table in the model.
For users who prefer to organize their models to ultra-high levels, having Measures scattered across multiple tables can be like having an itch you can’t scratch.
Power BI allows you to store all Measures in a “dummy” table, i.e., a table that contains only measures but no data fields.
Creating the Measures Table
To create the “dummy” table, select Home (tab) -> Date (group) -> Enter Data.
In the Create Table dialog box, give the new table a name like “Measures” and click Load.
NOTE: You can’t name the table “Measures” as that is a reserved word for Power BI.
We now have an empty table with a “dummy” field named “Column1”.
Moving Individual Measure to the Measures Table
To move a measure to a different table (like the newly created “My Measures” table), select the Measure then click the dropdown for Measure Tools (tab) -> Structure (group) -> Home Table and select the “My Measures” table.
The Measure has been moved to the new Measures table.
Bonus Trick #1: Converting the “Dummy” Table to an Official Measures Table
Because the “My Measures” table looks like every other table in the model, we can make it a bit more special by hiding the empty column named “Column1”. Right-click “Column1” and select Hide. The table has been moved to the top of the tables list and given an official Measures icon.
Moving Multiple Measures to the Measures Table
If you have many Measures that need to be moved to the “My Measures” table, moving them one at a time with the method described above can become tedious and time-consuming.
You can move multiple Measures in a single process by performing the following steps:
- Select the Model View.
- Expand the table(s) containing the Measures to be moved.
- Select the first Measure, press and hold the CTRL key, then select the remaining Measures.
- In the Properties panel, click the dropdown for Name and select “My Measures”.
All the selected Measures will now exist in the “My Measures” table.
Featured Course
Master Excel Power Query – Beginner to Pro
Tip #4: Creating Folder Hierarchies in the Measures Table
If you have many Measures in the Measures table, you can better organize the Measures by placing them in subfolders.
To do this, perform the following steps:
- Select as many Measures (using CTRL) as you wish to move.
- In the Properties panel, enter a name in the Display Folder field, like “YoY Percentages”.
We could perform the same process for creating a folder named “Totals”.
Switching to the Report view, our Measures are much more organized and easier to locate.
Tip #5: Custom Images for Page Backgrounds
It can be disappointing when put your heart and soul into creating a dashboard, then nobody wants to look at it because it’s not visually appealing.
We can spice things up by easily adding a background.
Backgrounds can be something as simple as a wallpaper-type image that serves to visually segment the stories being told in the report.
If you have an idea for segmented sections of a report, you can quickly produce the blueprint for the zones in PowerPoint.
These can be simple shapes that can be colored with greater capabilities than Power BI can offer. Options like gradients, patterns, images, shapes, etc.
Save the PowerPoint slide as a JPG or PNG file by selecting File (tab) -> Export -> Image Type.
You can add the PowerPoint image to the report by selecting the report page (not any specific object on the page) and clicking Format (paint roller icon) -> Page Background -> Add Image.
Set the Transparency option to 0 (zero) and the Image Fit option to whichever setting works best for your report. You may have to experiment with this option to get the best results.
You may also need to adjust the font colors of various objects on the dashboard to work best with your background color choices.
Tip #6: Customize Tooltips
Tooltips are those little pop-ups that appear when you hover over elements of certain visualizations.
Tooltips will display detailed information on the fields that make up the visualization. In the above case, the “Segment” and “Total Sales” details are displayed.
We can supplement the Tooltips with data from other fields by adding those fields to the Tooltips option in a visual.
Now we see “Total Quantity” information in the pop-up.
Creating Enhanced Tooltips
One of the best ways to impress your report viewers is to embed mini-reports into your Tooltip pop-ups.
Suppose we want to see the Top 5 Cities based on Quantity Ordered and sorted in Descending Order when we hover over a bar in the below visualization.
This can be achieved by following the steps below.
- Create a new sheet and give it a name (ex: “Quantity Breakdown”).
- In the Formatting panel (paint roller icon), expand the Page Information section and toggle the Tooltip option to ON.
- Expand the Page Size section and set the Type option to Tooltip.
- Select the View tab and set the Page View to Actual Size. This will ensure that you don’t create something too big that will be reduced to a smaller size for the report viewer, thus rendering it unreadable.
- Add a visualization then populate and customize it to your needs.
NOTE: For us to only show the top 5 cities based on the largest Total Quantity, we changed the Filter Type for City to Top N, then set it to show Top 5 based on “Total Quantity”.
- Return to the page containing the report.
- Select the visualization you want to display the Custom Tooltip within.
- Select the Formatting panel (paint roller icon) and expand the Tooltip
- Set the Type option to Report Page and set the Page option name of your page created in Step 1.
We now see the mini-report in the Tooltip pop-up when we hover over columns in the chart.
Tip #7: Page Navigation
Packing every visualization on a single sheet rarely works to your advantage. Using separate sheets to isolate different story elements is a great way to provide focus and reduce distractions.
The challenge is giving the user a convenient way to navigate from one sheet to another.
Power BI now offers a quick and easy way to generate what could be thought of as a Table of Contents using action buttons.
This is accomplished by adding a Page Navigator.
To insert a Page Navigator, select the page you wish to add this feature to then click Insert (tab) -> Elements (group) -> Buttons -> Navigator -> Page Navigator.
This will produce a set of buttons that are each linked to one of the pages in the report (see the left side of the following image).
NOTE: When developing a report in Power BI Desktop, you must press the CTRL key when clicking any item that is hyperlinked or provides an action. When published, the report viewer will only need to click the item to activate its behavior.
This set of navigation buttons can be placed on the first sheet only as a Table of Contents or placed on every sheet to give the impression of a universal set of navigation controls.
Customizing the Page Navigation Buttons
Below are just some of the customizations that can be applied to the Page Navigation buttons:
- Color of the buttons
- Orientation of the buttons (side by side or stacked)
- Shape of the buttons
- Size of the buttons
- Show buttons to hidden pages (or not)
- Show buttons to Tooltip pages (or not)
Tip #8: Enhance Page Navigation with a Back Button
If you are going to have the Page Navigator buttons located on the first page only, consider adding a Back button to the upper-left corner of each page to allow the user an easy way to return to the Table of Contents page.
These Back buttons can be added by selecting Insert (tab) -> Elements (group) -> Buttons -> Back.
This will add a button that the user can click to return to the Table of Contents page.
Tip #9: Lock Object
It’s a sad day when someone kicks over your sandcastle.
After spending hours designing the perfect report, the last thing you need is for someone to move or resize your visualizations.
This is usually unintentional, but the damage done is the same.
You can prevent this from happening by “freezing” the position of some or all visualizations on the page.
To do this, select a visualization (or press CTRL-A to select all visualizations on the page), then click View (tab) -> Page Options (group) -> Lock Objects.
Your viewers can now click to their heart’s content without accidentally corrupting your story.
This should become a step you perform towards the end of EVERY report your build.
Tip #10: Create a PDF Version of Your Report
If you need to send your reports to viewers who may not want something as sophisticated as a Power BI report, you can provide them with a PDF version of the report.
Granted, you lose all the interactivity that Power BI provides, but in some cases, that’s okay. A static version of the report is just what the user needs.
To generate a PDF version of your Power BI file, select File (tab) –> Export -> Export to PDF.
This will generate a PDF where each page of the report appears as a separate page in the PDF file.
This may take a few minutes to generate depending on the number of pages in your Power BI file.
The finished PDF will automatically open in your default PDF viewing application. From there you can save the file with the name and to the location of your choosing.
BONUS TIP: Copy Visualizations Into Other Applications
Once you have published a report to your Power BI workspace, you can easily copy any visualization for use in another program, like a Word document or PowerPoint presentation.
- Open the published version of your report.
- Hover over the desired visualization to reveal the Visual Header
- Click the COPY
After a few moments of processing, the visualization will be ready to copy to the Clipboard.
Switch to your other application and PASTE the visualization.
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
