Search

Search this blog:

Search This Blog

Tooltips in Power BI


The Power BI 'tooltip' is available in almost all Power BI visuals. What is a tooltip?

Tooltip: a message which appears when a cursor is positioned over an icon, image, hyperlink, or other element in a graphical user interface

screenshot Power BI visual with default tooltip

View the Live Power BI report here

Power BI Tooltips

Almost every visual, both built-in and custom, has a tooltip section of the fields that can be added to the visual. By default, any fields that are included in the 'Values' will automatically show up in the tooltip, but you have the ability to add additional fields to the tooltips. 

Default Tooltip

With the default tooltip, you have very little control over the order and format of the fields:

  • Fields used in the visual as 'Values' will be listed first, in the same order they are listed in the 'Values'
  • Fields used in the visual as 'Tooltips' will be listed under the 'Values' fields, in the same order they are listed in the 'Tooltips'
  • Fields used in the visual as 'Values' cannot be removed from the tooltip
  • Fields used in the visual as 'Values' must have the same format in both the visual and the tooltip

Now I have a few complaints with these settings for my current visual. I am only plotting the Auckland alert level, but would like the tooltip to display the alert levels for all of NZ, in a natural language friendly manner rather than the label and numerical value. 

screenshot default tooltip

With the tooltips feature I could add the natural language statement (see visual above for 'Lockdown March 2020 Key Event Value: Level 4 Nationwide') but I cannot remove the value used to plot the levels in my graph (see visual above for 'Lockdown March 2020 AKL Level: 4.00')

I would also like to reorder the fields so that the March 2020 stats are grouped (including the friendly natural language tooltip) and the current stats are grouped.

Report Page Tooltip

If we move from the 'Fields' to the 'Format' section of our chosen visual, we find a 'Tooltip' formatting option that can be expanded. This allows us to change the 'Type' to 'Report page'.

screenshot tooltip format options

Once you select the 'Report page' as the tooltip Type, a new dropdown will appear for 'Page'. All the other options below this Page dropdown apply only to the Default tooltips, so ignore them on Report page tooltips.

How to Create Report Page Tooltip

Step 1: Create Tooltip Page Format

Add a new page to your report. Click the Format paint roller to format the page: 

  • Page information: turn 'Tooltip' to 'On'
  • Page size: set 'Type' to 'Tooltip'


Step 2: Add Tooltip page Field

Add the same field used in the axis of your visual to the 'Tooltip' section of the Visualizations pane. This 'Tooltip' section only appears if you have done Step 1.

screenshot Power BI tooltip field options

Select a single value in this filter so you can visualize and test the tooltip design.

Step 3: Design Tooltip page

Add any visuals or information to your page. Keep in mind this is a tooltip, so it will be small and users cannot click or interact with it - so keep it simple! However, you can use conditional formatting, color, maps, charts or any visual tricks and features you want just like a normal Power BI report page. 

screenshot Power BI tooltip page

I've kept mine simple and just grouped the information I want in an easier to read format.

Step 4: Update Visuals Tooltip settings on other pages

By default, any visuals that use the field you added to the 'Tooltip' field will update to use the Report page tooltip instead of the default tooltip. In my example, any visual that uses the Days[Days In Lockdown] field will automatically start using my tooltip page instead of the default tooltip. 

If I create another tooltip page using that same field, Power BI won't know which to pick so to be safe, go find the visual where you want the tooltip.

Select the visual and click the Format to format the Report page tooltip options we discussed above: 

  • Change 'Type' to 'Report page'
  • Change 'Report page' to 'Tooltip Page Name' (where 'Tooltip Page Name' is the actual name of your tooltip page you created in Step 1)

Tooltips are Easy and Effective

That's all there is to it! Tooltips are an easy and effective way to make your report more meaningful and accessible to all audiences. 

Visual Review: Area Chart


I spend a lot of time using line and column charts, but in trying to overlay different types of information for different time periods, I have discovered that the Area Chart is really effective and underutilized (at least by me).

Today I'm using it to overlay two lockdown time periods and visualize the total number of COVID 19 cases in each period. 

View the live Power BI Report

Power BI Area Chart

Area Chart

Scores

Data Clarity: 8/10

Versatility: 8/10

Fun Factor: 7/10

PBI Certified: Yes (Built-in default visual)

Description

The Basic Area Chart is one of the original built-in visuals. It is useful for showing trends over time. Specifically it highlights the volume trending over time, because it shades the area under the line. If you remember back to high school calculus that area under the line/curve is the volume. Area charts do a great job of focusing the eye on the full volume, making it easier to see the big picture and compare values. I've found it's a good compromise to using cumulative values - just the simple act of shading the area under the line causes the human eye to include all the historical values with the current value and aggregate them. 

As you hover over the area chart, you also get a line showing where you are, which again makes it easy to compare the two regions to left and right of your cursor and see the difference in VOLUME for the two areas. 

How to

Fields

The Area Chart accepts data into the following field categories: 

  • Axis
  • Legend
  • Values
  • Secondary values
  • Small multiples
  • Tooltips

Axis

This should ideally be a date, time, duration or continuous (usually time based) field. In my example I have used 'Days in Lockdown' as my continuous time measure.

Legend

This can be used for categorizing your data. Either use Legend OR put multiple fields in the Values category. If you have something in legend, it will determine the different colors and lines (we call these 'series' in the data viz world). In my example I have opted to use multiple values, so must leave legend empty.

Values

Almost every Power BI visual has a values pane - put your explicit measures here. If your 'Legend' category is empty, you have the option to put multiple values in the Values category. In my example I have two measures in here to show the Alert Level in Auckland on any given day:

  • Alert Level AKL March 2020 
  • Alert Level AKL August 2021

Secondary values

This enables you to visualize data with different scales on the same chart. I have used it to overlay the Alert Levels behind the COVID Cases Reported. The highest alert level is 4, while the highest number of NZ COVID Cases Reported on a single day is over 80. If these were both plotted on the same scale we wouldn't be able to see the change in alert levels very easily. In my example, I have put the COVID Cases Reported in the secondary values:

  • Lockdown March 2020 Cases
  • Lockdown August 2021 Cases

Small multiples

This lets you create multiple charts without the need to copy paste. It ensures the scale and axes are consistent across all the visuals, ensuring that your report reader can safely compare and pick up on patterns across the visualizations. It's a relatively new feature that has been added to all the core Power BI visuals and I have blogged before about Small Multiples

Tooltips

Tooltips are also available on most Power BI visuals - I will be doing a separate blog on Tooltips where I compare the built in tooltips to Report Page Tooltips.

Formatting Options

Once you've got the data into the visual, it's time to format it nicely. Select the visual, then click on the format paint roller to view the formatting options. 

Screenshot Power BI Area Chart formatting options

General

For most visuals this has the responsiveness, size, position, layer order and alt text. The Area Chart General formatting (and line chart too) have an extra option in here for High Density Sampling

> High Density Sampling

High Density Sampling is a feature that ensures Power BI visuals capture all the outliers in your data and provide a true representative sample even when working with many data points. By default this is set to 'On' and you should leave it that way.

In the visual below, you can see that with high density sampling turned on, there's a break in the pink negative areas. With high density sampling turned off, that break is missed because the visual is only plotting regular samples at specific time periods, and misses the maximum value in that range. The high density sampling algorithm ensures that the min and max values are always plotted, and therefore we see the more accurate representation of the data which indicates a break in the negative pink area:

GIF high density sampling turned on and off

Legend

Screenshot of Power BI Area Chart Legend

The 'Legend' section will only appear under formatting options if you have either:

  • A field in the 'Legend' category, OR
  • Multiple fields in the 'Values' category, OR
  • Multiple fields in the 'Secondary values' category, OR
  • A field or fields in both the 'Values' and 'Secondary values' categories

Screenshot Power BI Legends

I particularly like to play around with the 'Style' section of the Legend. Depending on what options you select further down (in Shapes section) you may want to change the default Style.

X axis

By default, this will be set to 'Continuous' which will ensure you can see the entire time range on one screen, but may also result in some sampling, so make sure you've selected the right 'High Density Sampling' option from the General section when Continuous is set to on. 

This dataset has been curated so I decided how many days I wanted to visualize, therefore the 'End' of x axis set to auto will plot all 100 days I created in my GENERATESERIES column for the x axis. You may wish to use the Conditional formatting option here to find the last point in time with data and ensure the x axis stops there. Wonderful power and flexibility! You can even apply conditional formatting to the axis labels (for example on a categorical axis you could change the color for weekends vs weekdays - how cool is that?)!

Y axis

The Y axis settings are mostly a mirror of the X axis. If you're using multiple fields in the 'Values' you may want to turn on and change the Title of the Y axis so that people know which numbers relate to which areas in the chart.

Secondary Y Axis

These settings only appear if you're using the 'Secondary Values' and are the same as the Y axis settings.

Zoom sliders

This is a relatively new feature that I'll leave for another post on another day, but basically it lets the user change the axes start and end values dynamically to focus on what they want to see.

Data colors

Unfortunately, there is no conditional formatting for data colors in the Area Chart, which is one of the things keeping it from getting a higher score in versatility. It's possible to set the data colors specific to each category or series if you're using multiple values or legend.

Data labels

Data labels can be customized by series - just scroll to the bottom and turn customize series to 'on' (will only work if Legend is a formatting option). 

Screenshot Power BI Data Labels two options

An important thing to note - the data labels don't enable you to show the series label for multiple values fields, but you can change the color so that it's easier to tell which numbers relate to which series. Which of the above images do you prefer? I don't like using color as the only distinguishing feature, so this is another reason this visual doesn't get higher scores for data clarity.

Shapes 

This might be where the fun factor comes in, but it's also a really useful way to differentiate between your series. 

Power BI Area Chart Shapes format options


I'm using the area chart to compare two distinct time periods - and to forecast based on previous values. Therefore I want the shapes for the previous 'forecast' to be dashed, making the actual current values stand out more. You can also adjust the stroke width and turn on markers to distinguish data points from the line connection. If you have stepped data (such as my Alert Levels) that you don't want a diagonal line for, turn the stepped layout to 'On'.

Plot area

This just contains the standard plot area format options - adjust the transparency or add a background image. 

Note: this is different to the Background image and transparency - plot area does not include the legend and title of the visual.

Title

Includes all the beautiful title formatting options, including conditional formatting and word wrap if you have long titles or tiny screens.

Background

This is where you set the transparency and image for the entire visual - useful if you have something behind this visual that you want to hide or show (which sometimes happens when I'm creating mobile only visuals since Power BI has no option to hide a visual in desktop but show in mobile). 

Otherwise I leave these as defaults.

Lock aspect

Turn this on if you want the visual to behave like images in Word or PowerPoint. 

When Lock aspect is set to Off (the default), you can grab the corner of your visual and drag it anywhere - changing the aspect ration of the visual as you do. 

When Lock aspect is set to On, you can grab the corner of your visual and it will only let you drag it along the diagonal of the existing visual - thereby maintaining the current aspect ratio. 

If you resize using the side, top, bottom or General section of the visual Lock aspect has no impact. 

Border

If you turn this on, you can add rounded corners by adjusting the 'radius'.

Shadow

Make sure you leave space between your visuals if using the Shadow effect. 

Tooltip

This is how you can change what the additional information that shows up on hover looks like - I'll detail these settings in a separate post because they are very cool and deserve their own post.

Visual header

A while back the visual header was updated to a new look and feel, and now you can control which features show up at the top of the visual when users hover or select the visual. 

Note: This only impacts the header behavior in PowerBI.com so you need to publish the report to see the impact of the visual header changes.

Power BI Forecasting with irregular time periods


Will Auckland move to Alert Level 3 on Monday?

Power BI screenshot of COVID lockdown levels and case numbers

It's day 31 of Lockdown Level 4 for us Aucklanders (how did that happen and what have I done with all that time?!). Monday we will find out if Auckland stays in Level 4 or moves to another alert level.

This is already Auckland's longest time in Level 4, and it seems everyone is getting stir crazy; We've all walked our neighbourhoods so many times we know every nook and cranny by heart. It's been rainy the past week or so, so we've also probably finished all the puzzles in the house (twice), read plenty of books, watched the Top 10 on Netflix, run out of flour from excessive baking and put on a few kilos. Well, that's been my lockdown anyway. 😂

After all that activity, I'm left wondering what Monday's announcement will bring for us Aucklanders; will we get to enjoy someone else's cooking skills with Lockdown Level 3? (See my Lockdown with Takeaways post if you're not from NZ.) Or will we stay in Level 4 a bit longer to beat the delta variant? (If so, send me your favourite recipes - I need new ideas!)

I have been wanting to overlay the current case numbers with the last big lockdown we had in Auckland, so finally got around to making it happen. 

View the Live Power BI report

DAX Data Overlay for Forecasting

Power BI has some built in forecasting that will automatically calculate and forecast, allowing you to change the number and length of periods, as well as the seasonality. However, this doesn't work for irregular time frames. COVID lockdowns in New Zealand don't really have a seasonality. It also doesn't allow you to overlay past actuals at the same time as forecasting, so I decided to create some crazy DAX measures to show what I wanted.

GENERATESERIES: Days since start of Lockdown

The first calculation I made was to create the X-axis: Number of Days in Lockdown

DAX has an easy function that can make this axis for you. Click New Table to create a new table for this calculation: 

Days In Lockdown =

GENERATESERIES (

    -10,

    100,

    1

)

This creates a single column table with numbers ranging from -10 to 100, increasing by 1 each row. I decided to start at -10 because the first lockdown in NZ started about 10 days after the first reported case, so this would ensure we could see that history in the graph.

Key Dates table

The next step was to list all the key dates and alert levels throughout both lockdowns. I know that I can use approximate lookup in DAX to display the current level if I just provide a list of Alert Level start dates, which is conveniently compiled on the Timeline of Key Events COVID19.govt.nz website.

table of key COVID19 NZ Dates

This table should NOT be related to any of the other tables in the data model - we'll make all the magic happen with DAX.

Along with key dates table, I created two measures for the start date of the two periods I wanted to compare: 

Key Start Date Period to Compare

Lockdown March 2020 Start Date =

DATE ( 2020323 )

Key Start Date Current Period

Lockdown August 2021 Start Date =

DATE ( 20210817 )

DAX Variables and Approximate Lookup: Convert Date to relative time since Start of Lockdown

I'm comparing to the start of lockdown, but you can use this method to compare any two time periods by relative length/duration. We simply need to use the value of the GENERATESERIES column we created, add that to the value of the Key Start Date value. I'm calling this value my 'CaseDate', but it's the date in your Fact table. 

Key Date Approximate Lookup

For the alert level status, we need to use the DAX approximate lookup technique, I have chosen the MAXX(FILTER( option: 

Lockdown August 2021 AKL Level =
VAR _StartDate = [Lockdown August 2021 Start Date]
VAR _Duration =
    MAX ( 'Days In Lockdown'[Days In Lockdown] )
VAR _CaseDate = _StartDate + _Duration
VAR _KeyDate =
    MAXX (
        FILTER (
            'NZ Key Dates',
            'NZ Key Dates'[Date] <= _CaseDate
        ),
        'NZ Key Dates'[Date]
    )
RETURN
    IF (
        _CaseDate
            <= TODAY (),
        SUMX (
            FILTER (
                'NZ Key Dates',
                'NZ Key Dates'[Date] = _KeyDate
            ),
            'NZ Key Dates'[AKL Level Number]
        )
    )
 

This will allow us to calculate the Alert Level in Auckland on each relative day since the Key Start Date of Lockdown. 

Power BI levels overlay chart

Key Date Exact Lookup

The DAX for the Case numbers is a bit simpler, since we have a value for each date that has cases (unlike the Key Dates table which only contained the Start date value). Therefore we don't need the MAXX calculation and can do an exact lookup: 

Lockdown August 2021 Cases =
VAR _StartDate = [Lockdown August 2021 Start Date]
VAR _Duration =
    MAX ( 'Days In Lockdown'[Days In Lockdown] )
VAR _CaseDate = _StartDate + _Duration
RETURN
    IF (
        _CaseDate
            <= TODAY (),
        COUNTX (
            FILTER (
                'NZ Cases',
                'NZ Cases'[Report Date] = _CaseDate
            ),
            'NZ Cases'[Index]
        ) + 0,
        "TBC"
    )

This allows us to calculate the total case numbers on each relative day of lockdown since the Key Start Date: 

Power BI chart cases overlay

Then put them both together in an area chart and voila! Easy to see the patterns and similarities and differences between the two time periods. 

Sunday's spike of 40 new cases is concerning for what Monday's decision may bring, but only time will tell the actual result. For now, I'll content myself with forecasting and playing with Power BI visuals.

Custom Visual Review: Charticulator

This is not your ordinary custom visual - this is EVERY custom visual. Charticulator puts the power to design and develop custom visuals to ...