• 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
    • 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
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Using Jitter to Avoid Over Plotting in Power BI

You are here: Home / Excel Charts / Using Jitter to Avoid Over Plotting in Power BI
using jitter to avoid over plotting
January 11, 2020 by Philip Treacy

If you need to plot data that has one variable where values can be the same or very similar, for example the age of a group of people, you'll likely end up with data points that are plotted over the top of each other.

To make this type of plot easier to read and allow the reader to gain better understanding of the data, we can use jitter.

Overplotted Data

over plotted data without jitter

Jittered Data

scatter plot with jitter

Jitter means adding a small movement to the plotted point to make all the points easier to see. In this case we can move the points a little to the left and right.

The underlying data isn't changed, just the plotted point's position.

Using Jitter in Power BI

There are a few options here. You could use Excel to create another column in your data with the jittered values before loading your dataset.

Or you could use Power Query/DAX to calculate the jittered values once data is in Power BI.

Both approaches require you to know beforehand that you need to jitter the data, and to do extra work to calculate this jitter. But if you use some native Python visualizations, this work is done for you and it's easy to turn jittering on and off.

Download PBIX File and Dataset

Enter your email address below to download the sample file and data.

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

Download the PBIX file to load into Power BI.

Download the dataset

Python Visualizations in Power BI

Power BI supports the use of Python to create visualizations so this is very useful if Power BI doesn't already support the type of visualization you want to use or if you can't find a good custom visual to meet your needs.

Actually using a Python chart isn't as complicated as you may think. You follow the usual steps to get your data into Power BI then drag the fields into the Values area. A few simple modifications to the Python code and that's all you need.

But before we get into Power BI, you need to make sure you have Python installed on your computer so that you can write the code in Power BI Desktop.

You can download and install Python from the main Python.org page.

Python Visualizations in Power BI

Running Python Scripts in Power BI Desktop.

Creating Power BI Visuals with Python

Python Visualizations in Power BI Service

Creating the Plot

With Python installed the first thing you want to do after starting Power BI Desktop is load the dataset from a CSV file.

I'm using a dataset that shows, amongst other things, the total bill for meals at a restaurant over a four day period, Thu - Sun.

With the dataset loaded, click on the Python visual icon

Python visual icon in Power BI

Power BI will ask you to enable script visuals so click on the Enable button.

enable script visuals

Now drag across the day and total_bill values and set them both to Don't summarize.

Don't summarize values in Power BI

When you drag fields into the values area, you'll see the Python script editor appear at the bottom of the window, this is where the visualization code goes.

When your Python code is ready, you click on the Run script button to draw the visualization.

Python Script Editor - Run script button

Python in Power BI works with a data structure called a DataFrame and this is automatically created for you. Think of the DataFrame as a table. PBI names this DataFrame dataset and it contains the data in the Values area.

If you have a field called day, the Python script accesses the data in the day column by using the term dataset['day'].

For this visual I'm using a Strip Plot from the Seaborn visualization library. A Strip Plot is essentially a scatter chart for categorized data. Along the x axis are the days (the category) and on the y axis is the bill amount. The code to draw this is just

Seaborn code without jitter

We end up with a chart like this

Strip plot without jitter

You can see that because we have so many data points of similar value we get overplotting. This doesn't give a good feel for the frequency or distribution of the data.

Adding Jitter

To add some jitter just make the jitter parameter True

Seaborn code using jitter

and you end up with this plot

Strip plot with jitter

It's now easier to see how many data points we have, but we can improve things further by making the points a bit bigger and by making each marker's outer edge white. This will let us see more clearly where points are still plotted over each other.

NOTE : Each time you replot the chart the jitter is recalculated, so the points will end up in different positions each time you run the script.

Our plotting code is now

Strip plot marker style

resulting in

Strip plot with jitter and edgecolor

The default for a strip plot is for jitter to be on, but you may not always want to use it, and for the sake of this example I'm explicitly turning it off and on to demonstrate its effect.

Summary

When you have data points plotted over the top of each other, jitter is useful to spread those points out and let's you understand the data better.

Download the sample PBIX file and dataset (above) and give it a go yourself.


Further Reading

Seaborn Visualization Examples

using jitter to avoid over plotting

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.
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.
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.
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 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
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.
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

More Python Posts

charting real time data in excel

Charting Real Time Data in Excel

Receive data in real time and chart the data as it arrives. Can be used to chart things like stock prices or sensor readings. Sample code and workbook
Real time data in Excel using sockets

Real Time Data in Excel Using Sockets

Get real time data into Excel using Python web sockets. Once established, sockets stay open and require little overhead to send messages.
real time data in excel

Real Time Data in Excel

Get real time data like stock or currency prices into your Excel workbook using a little bit of Python code. Sample workbook and code available.
writing udfs in excel with python

Writing UDFs in Excel With Python

Write Excel UDFs in Python. Use the power of the vast Python libraries for data science, statistics, web scraping or database connectivity.
Plot an equation in Excel using Python

Plot an Equation in Excel Using Python

How to plot an equation in Excel using Python. Easily plot your functions or data with just a few lines of code. Sample workbook and code available.

More Excel Charts Posts

excel speedometer charts

Excel Speedometer Charts

How to build Excel Speedometer Charts or Gauge Charts as they're also know, why they are BAD and what to use instead.
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.
chart axis switch

Excel Chart Axis Switch

Use radio button form controls to create an Excel chart axis switch enabling you to toggle pannel charts between same axis and own axis.

Excel Charts with Shapes for Infographics

Excel Charts with Shapes for Infographic styling and increased interest in your charts. Easy to insert but there are a few tricks required.
excel pyramid chart

Excel Pyramid Charts

Excel Pyramid charts are useful for visualising demographic data across multiple categories. Let’s look at 3 ways we can build them in Excel.

Highlighting Periods in Excel Charts

Highlighting Periods in Excel Charts helps your users interpret them more quickly and or focus their attention on a point or area.
stacked bar waffle chart

Stacked Bar Excel Waffle Charts

Stacked Bar Excel Waffle Charts are an alternate to using conditional formatting to build waffle charts, and some say they're easier.
Category: Excel ChartsTag: charts, Power BI, Python
Previous Post:Excel XLOOKUP Function
Next Post:List First Monday Date in Each Monthlist_first_monday_date_in_each_month

Reader Interactions

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...

Shopping Cart

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.

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
  • 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
 
  • About My Online Training Hub
  • Contact
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

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.

Download A Free Copy of 100 Excel Tips & Tricks

excel tips and tricks ebook

We respect your privacy. We won’t spam you.

x