• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Highlighting Data in Power BI Visuals

You are here: Home / Power BI / Highlighting Data in Power BI Visuals
highlighting data in power bi visuals
April 29, 2021 by Philip Treacy

In this post I'll be looking at ways to highlight or label values that may be of special interest in visuals.

Perhaps you are interested in seeing the minimum and maximum values, or some data points of significance like when your business held a promotion.

Or you might want to highlight points that fall within a certain range.

The first couple of examples use line charts, then I'll look at a clustered column chart and finally a scatter chart.

Watch the Video

Subscribe YouTube

 

Source Data

This is the source data I'm using, it's totally made up and consists of 2 columns of values for the 12 months in 2021.

source data

Download PBIX Desktop File

Enter your email address below to download the sample files used in this post.

By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Power BI Desktop File.

 

Highlighting Data in a Line Chart

First off let's create a line chart and add the Date and Value1. Make sure the date is set to just the date and not a date hierarchy

add line chart

add date

line chart

If I want to know the min and max values it's not immediately apparent what these are. There are several dips and peaks that could be the values I want.

To highlight the min and max I first need to create a column for each one. This can be done either in Power Query or in DAX.

I'll show you how to do both so let's start by going in to the Power Query editor by clicking on Transform data.

transform data

Add a Custom Column

add custom column in power query

I'll create a column for the minimum value so the code is this

custom column code

if [Value1] = List.Min(#"Changed Type"[Value1]) then [Value1] else null

Remember that a column is a list so you can check for the minimum in that list using the List.Min function and referring to the previous step #"Changed Type" and column name [Value1]

Click OK to close this and create the column gives this

min value column

Don't forget to set the column data type to Whole Number

set column data type

Close and Apply to load the data into the Power BI Data Model

close and apply

The new Min column can be added to the values

add column to visual

and the chart now shows our minimum value

min value on chart

Now to create the Max column in DAX, go to Modelling -> New Column

add dax column

The column will be named Max and the code to create it is this

dax code for new column

With the max column created, add it to the chart and now both min and max values are clearly shown.

max value on chart

You can change the colors for min and max under Data Colors if you want to.

data colors

Under Shapes, reduce the stroke width to make the line thinner and the dots stand out more.

line width

line chart

To make it even better, turn on data labels

turn on data labels

Open the Data Labels section and scroll down, turn on Customize Series. Turn off labels for the main values - Value1.

customize series

The label for min is obscured by the line so change that so the label position is Under

min label obscured

move label under

label unobscured

There's one more thing you can do to indicate the min and max even more clearly and that's use Custom String Formats for the Min and Max values.

Click on the Min column and in the Format area of the Ribbon type in a custom format string 0" (Min)"

custom format string

This displays the Min value like this

format string for min value

Similarly for the max, click on the Max column and enter the custom format string 0" (Max)"

I've turned off the Legend, and adjusted the y-axis range and the final chart looks like this.

final chart wth min and max

Highlight Events on a Line Chart

Next I want to look at how to label specific values that correspond to an event for example, when your business runs a promotion.

The process if very similar to what i just did for Min / Max values.

Starting with the same data, create a line chart,add the Date (not as a hierarchy) and Value1.

My pretend business had (or will have) promotions in May and August. I need to create a new column to indicate this and again this can be done either in Power Query or in the Data Model using DAX.

Let's do it with DAX: on the Ribbon go to Modelling - > New Column and enter this code

dax column for promotions

Promotions were held in May and August so I need to check the month in the Date column. The MONTH function returns 5 for May and 8 for August.

The code means, if the date is either in May or August, add the value corresponding to that date to the new Promotion column.

With the column created, add it to the chart.

line for promotions

and I get this line for the Promotions, but I don't want that.

OK, with the chart visual selected, go to Shapes -> turn on Customise Series.

shapes customize series

Select Promotion and set Stroke width to 0

set stroke width

Turn Show marker on, and change the color to something that stands out, I went with pink.

turn on markers

If you use the Legend then you need to match the Promotion series color and the marker color. You can change the series color under Data Colors.

legend color

Or you can turn off the Legend and use a custom format string for the Promotion dates.

As I did in the previous example, turn on Data Labels, then scroll down and turn on Customize Series, and don't show labels for Value1.

turn off labels for main series

Select Promotion, make sure labels are on, and set the label position to Above

set promotion label to above

Select the promotion column and enter "Promotion" in the Format area of the Ribbon.

custom format string for promotion

The final chart looks like this

finished chart wth promotions

Line and Column Chart

Next up is highlighting values in column charts, or line and clustered column visuals as they are in Power BI.

Using the same data, add a line and column visual, and add the Min and Max to the Line values.

line and clustered column chart

You can turn on Data Labels then customise each series. Turning off labels for the main Value1 series, and change the Data Color for Min and Max. Turn off the Legend.

formatted line column

The Min and Max are displayed with the Custom format string I applied earlier but this visual can be made better.

I'm really only interested in seeing the min and max values so I'm going to change the color of the columns to make it quicker and easier to see the min and max.

Under Data Colors, I can apply a function for the default color - this is essentially conditional color formatting.

But to do this I need to create a column that contains the colors I want to apply to the columns.

So, Modelling -> New Column, call the new column ColumnColors, the DAX code to create it is

dax for color column

I'm using Hex color codes here for the column colors. These color codes are comprised of a mixture of red, green and blue values in hexadecimal.

The first 2 chars are the red component, the second 2 the green and the last 2 are the blue e.g. #RRGGBB

There are plenty of web sites that allow you to choose a color and gives you the hex code, just do a search in your web browser for hex color.

Or under Data Colors, click a color then click on More colors and pick a color, it shows you the hex code for that color.

hex color code picker

Back to my DAX for this new column, I've chosen purple for Max and a reddsish brown for the Min.

The last color, #CCC, is the default for all the other bars, which is grey. I've used shorthand here. #CCC is the same as #CCCCCC.

When the column is created I get this in my data table

column of colors

With the color column created I can now use it to color the visual. Click on the visual and under Data Colors, click on the fx button beside Default color.

fx button for colors

Use these settings : Format by -> Field value, Based on field -> ColumnColors and Summarization -> First.

color formatting columns

Click OK to apply these color settings and the min and max values are now immediatley obvious.

colored column visual

Because Min and Max are Line Values they have their own color for the line and this is currently different to the column color.

Under Data colors you can either change these to match the column colors, or make them white (to match my background) as I have.

white markers

Scatter Chart

The last visual I'm going to look at is the scatter chart. Add one and then plot Value1 against Value2. Don't summarize either value.

After setting the axis limits to appropriate values I end up with this.

scatter chart

I want to highlight points where Value1 is between 40 and 80 and I'll need to create a column containing colors to do this.

For this column I'll use color keywords rather than the hex color codes. You can use any CSS color which you can find at the w3.org website.

Add a new column from Modelling -> New Column and call it ScatterColors

scatter chart colors

The points I'm interested in will be colored orangered and everything else will be steelblue.

scatter colors column

With the column created, select the scatter chart, go to Data Colors and click the fx button.

scatter default colors

The settings here are almost identical to the settings for the line/column chart, except you select ScatterColors in the Based on field.

set scatter colors

Click OK and right away the points of interest stand out.

highlighted points in scatter chart

Summary

Hopefully you've found something useful in this post that you can apply to your own Power BI visuals.

highlighting data in power bi visuals

More Charts Posts

burn up burn down charts

Excel Project Management Burn Down and Burn Up Charts

Excel Burn Down and Burn Up Charts are easy to make with line or scatter charts. They are useful for monitoring the progress of a project.
wee people font charts

Excel WeePeople Font Charts

Excel WeePeople Font Charts are a nice change from generic shapes for waffle charts, bar/column charts and more.
excel dot map charts

Excel Dot Map Charts

Interactive Excel dot map charts are not built-in, but with some creative use of Excel’s built-in tools we can create something unique.
Excel S Curve Charts

Excel S-Curve Charts

Easy Excel S-curve Charts made with PivotTables for project management. Track progress by including budget amounts.
shape maps in power bi

Shape Maps in Power BI

Shape maps in Power BI can be used to show the distribution of a variable across geographic regions. Learn a trick to plot discrete data too.
using jitter to avoid over plotting

Using Jitter to Avoid Over Plotting in Power BI

Plotting data that has one variable where values are similar, can result in points that are plotted over each other. Use jitter to avoid this overplotting.
Excel custom chart labels

Excel Custom Chart Labels

Create dynamic Excel custom chart labels with this category axis hijack trick
Sorting Excel Date Slicers

Sorting Excel Date Slicers

Slicers have some shortcomings when it comes to dates and sort order. This post explains a couple of ways to sort dates correctly in Excel slicers.
Charting Disparate Data in Excel – 3 Solutions and 1 Crazy Mess

Charting Disparate Data in Excel – 3 Solutions and 1 Crazy Mess

4 Charts – Same Data. Which do you think is best?

4 Charts – Same Data. Which do you think is best?

More Excel Charts Posts

burn up burn down charts

Excel Project Management Burn Down and Burn Up Charts

Excel Burn Down and Burn Up Charts are easy to make with line or scatter charts. They are useful for monitoring the progress of a project.
wee people font charts

Excel WeePeople Font Charts

Excel WeePeople Font Charts are a nice change from generic shapes for waffle charts, bar/column charts and more.
excel dot map charts

Excel Dot Map Charts

Interactive Excel dot map charts are not built-in, but with some creative use of Excel’s built-in tools we can create something unique.
animating excel charts

Animating Excel Charts

Use animation correctly to enhance the story your data is telling. Don't animate your chart just for some eye candy. Sample code and workbook to download.
project management dashboard

Excel Project Management Dashboard

Excel project management dashboard video tutorial covering various techniques including conditional formatting, PivotTables, Slicers, charts and more.
jitter in scatter charts

Jitter in Excel Scatter Charts

Jitter introduces a small movement to the plotted points, making it easier to read and understand scatter plots particularly when dealing with lots of data.
Custom Excel Chart Label Positions

Custom Excel Chart Label Positions

Custom Excel Chart Label Positions using a dummy or ghost series to force the label position neatly above the columns of data
Lookup Pictures in Excel

Lookup Pictures in Excel

Lookup Pictures in Excel using values in cells returned by data validation lists (drop down lists) or Slicers. No VBA/Macros required!

Cross Highlight Excel Charts

Filter and Cross Highlight Excel Charts like you can in Power BI using some Excel Power Pivot magic, regular charts and a Slicer.
save chart as image

Save Chart as Image

List all the charts in your workbook then select the ones you want to save as either PNG or JPG. Sample workbook and code to download

More Power BI Posts

combine files with different column names in power query

Combine Files With Different Column Names in Power Query

Learn how to load data into Power Query when the column names in your data don't match up. Sampe files to download.
try otherwise power query iferror

IFERROR in Power Query Using TRY OTHERWISE

Using TRY..OTHERWISE in Power Query Replicates Excel's IFERROR So You Can Trap and Manage Errors In Your Queries.
easily compare multiple tables in power query using list functions

Easily Compare Multiple Tables in Power Query

Compare tables or lists in Power Query using List Functions. This method is great when dealing with 3 or more tables or lists.
fuzzy matching in power query

Fuzzy Matching in Power Query

Use fuzzy matching to compare non-identical text strings and match them together based on how similar one string is to the other.
handling http errors in power query and power bi

Handling HTTP Errors in Power Query and Power BI

Clearly communicate issues with custom messages when dealing with web scraping or API server errors. Download sample Excel and Power BI files
extract characters from strings in power query using text select and text extract

Extract Letters, Numbers, Symbols from Strings in Power Query with Text.Select and Text.Remove

Learn a cool technique to extract or remove letters, numbers and special characters from strings. Sample workbook to download
shape maps in power bi

Shape Maps in Power BI

Shape maps in Power BI can be used to show the distribution of a variable across geographic regions. Learn a trick to plot discrete data too.

Converting Decimal Time to Days, Hours, Minutes, Seconds in Power BI

Convert times and durations from decimal numbers to easily understood formats like hh:mm:ss. Sample code and file to download.
sort by column in power bi

Sort-By Columns in Power BI

Create a Sort-By column to allow custom sort order in your Power BI Visuals. Download an example Power BI Desktop file
custom formatting strings in power bi

Custom Formatting Strings in Power BI

Control how data is displayed in Power BI using your own formats. Like hiding negative or zero values, using symbols or custom number formats

More Power BI Posts

custom tooltips in power bi

Custom Tooltips in Power BI

Create custom tooltips for Power BI visuals. Use charts, images, numeric or categorical data to enhance the information your visuals convey
conditional formatting tables and matrices in power bi

Conditional Formatting in Power BI Tables and Matrices

How to apply conditional formatting to tables and matrices in Power BI. Use color, icons, data bars and URL's.
dax editor keyboard shortcuts

DAX Editor Keyboard Shortcuts

Keyboard shortcuts for the DAX editor in Power BI. Learning DAX is hard enough. Make your life easier by using these keyboard shortcuts.
fixing incorrect totals in dax

Fixing Incorrect Totals in DAX

If you're getting incorrect totals in your DAX measures, this post explains how to fix them. VIDEO and Sample file to download.
highlighting data in power bi visuals

Highlighting Data in Power BI Visuals

Learn several techniques to highlight or label important data points in your Power BI visuals. Sample file and code to download.
Power BI Organizational Data Types

Power BI Organizational Data Types in Excel

Power BI Organizational Data Types in Excel revolutionize the way we store and access our data with one central location and small files.
shape maps in power bi

Shape Maps in Power BI

Shape maps in Power BI can be used to show the distribution of a variable across geographic regions. Learn a trick to plot discrete data too.
static data tables

Static Tables in Power Query, Power Pivot and Power BI

Use static tables to store data in Power Query, Power Pivot and Power BI without needing to load data from an external source

Converting Decimal Time to Days, Hours, Minutes, Seconds in Power BI

Convert times and durations from decimal numbers to easily understood formats like hh:mm:ss. Sample code and file to download.
Power BI Dashboards

How to Build Power BI Dashboards

This video tutorial takes you step by step through building Power BI dashboards and reports, including publishing and sharing.


Category: Power BITag: charts, excel charts, Power BI
Previous Post:Secrets to Building Excel Dashboards in Under 15 Minutes
Next Post:Share and Collaborate in Excel

Reader Interactions

Comments

  1. Jes Rigby

    May 9, 2021 at 1:42 am

    hi Mynda, nice site! Just a small extra tip for viewers of your videos and problems keeping up with your prodigious work rate.

    Viewers can simply click the cog symbol at bottom-right of the video frame and reduce the playback speed to 50% or even 25%.
    As well as getting much more opportunity to take in what you’re saying and perhaps see the keyboard shortcuts flashing up and other settings in the menus that you are clicking your way through, we also get an insight into what you might sound like in 15 or 30 years!

    Personally, I find it easier and less frustrating than the rewind and replay method although I don’t really suggest anyone watches whole videos at those rates.

    Thanks again for a great site. Sorry if you/someone already suggested that elsewhere.

    Reply
    • Mynda Treacy

      May 9, 2021 at 10:10 am

      Thanks, Jes! Yes, the slower playback speed is a great idea that I suggest to people when they complain that I speak too fast, so thanks for sharing it here. I haven’t listened to it at slow speed…not sure I want to hear what I sound like in 15 years! 😀

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Featured Content

  • 10 Common Excel Mistakes to Avoid
  • Top Excel Functions for Data Analysts
  • Secrets to Building Excel Dashboards in Less Than 15 Minutes
  • Pro Excel Formula Writing Tips
  • Hidden Excel Double-Click Shortcuts
  • Top 10 Intermediate Excel Functions
  • 5 Pro Excel Dashboard Design Tips
  • 5 Excel SUM Function Tricks
  • 239 Excel Keyboard Shortcuts

100 Excel Tips and Tricks eBook

Download Free Tips & Tricks

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.