How to add sparklines to highlight details in Microsoft Power BI

Microsoft Power BI logo on phone screen stock image.
Image: sdx15/Adobe Stock

Charts are a great visual tool for sharing information; share the history of your data with a quick glance. For Power BI, charts are everything, and there are plenty to choose from. One of the easiest to create is the sparkline visual.

Power BI embeds small cell-level charts into the table or visual matrix. They are easy to add and have a lot of impact.

In this tutorial, I’ll show you how to add sparklines to a visual in Microsoft Power BI Desktop on a Windows 10 64-bit system.

What are sparklines?

Sparklines are tiny charts that are embedded in a data table. If you’re familiar with Microsoft Excel, you may have already encountered them. They have been around for a long time and are popular because they fit in a single cell right next to the source data.

Viewers get a quick look at trends, growth, min/max values, etc. They are easy to implement considering the story they tell about your data.

Choose Sparklines when you want to display the details stored in a table with a chart that displays minimal information. Power BI typically hides the X-axis, and given the space constraints in a cell, won’t use it for detailed analysis.

Before we create sparklines, we need data. In this example, we will import a simple Microsoft Excel sheet.

SEE: Microsoft Power Platform: What you need to know about it (Free PDF) (TechRepublic)

How to import Excel data into Power BI

We will import the simple Excel sheet shown in Figure A. If you’re following the demo .xlsx file, save it to OneDrive or SharePoint and note the location.

Figure A

Data sample in Excel including date, region, and amount information
Figure A: We will add sparklines to this simple sheet of data imported from an Excel .xlsx file.

To import this data, start Power BI as you normally would and close the info window. In the default window, click the Excel icon next to Get Data in the menu. In the resulting Open dialog box, browse to the demo file, PBIImportFromExcel_SourceFile.xlsx (Figure B), or the file you want to use instead.

Figure B

Select an Excel file to open in Power BI
Figure B: Open the Excel file.

Click Open to import the data into Power BI. The Excel file has a sheet and a Table object. In the resulting Navigator, check TableSales (Figure C) and then click Upload.

Figure C

TableSales data source in Power BI
Figure C – Check the data source, TableSales.

With the data available in Power BI, let’s build a visual.

How to build the visual in Power BI

Power BI supports sparklines on table and matrix images, which makes sense because Power BI embeds the tiny charts in the cells.

To create a simple matrix visual, choose Matrix in the Visualizations panel. Fill the matrix by dragging Region and Date to the Rows bucket and drag Quantity to the Values ​​bucket.

Figure D displays the resulting array, which requires only a bit of explanation.

Figure D

Data visualization menu in Power BI
Figure D: The matrix groups the data by region and then by date.

Expand any region in the array and you will find the grouped dates for that region (Figure E). Click the plus sign to the left of a region to display the first subset, which is the year.

Figure E

Microsoft Power BI data that includes values ​​for region and quantity based on a date
Figure E: Dates are a group within the region group.

Thanks to Power BI time intelligence and the underlying date table that Power BI automatically creates, you have multiple time components to filter on.

If the date table isn’t familiar to you, consider reading How to tell if the automatic date table is appropriate when using Power BI, later. You don’t need to complete this article, but it’s something you’ll want to learn if you’re going to use Power BI.

Continue clicking the plus signs to display all the time components for a particular region, so you can see how much information is automatically in your visual. I’m also working in focus mode, which makes the image more readable.

Notice that I applied currency formatting to the Amount column. You can do this in the Report window, but it’s much easier in the Data window. Using the Visualizations panel, it’s a few layers deep, and once you find the options, they’re a bit confusing.

Click the Data icon on the left. In the Data window, click the Amount heading, and then click the Currency ($) icon on the menu. Then click the Report icon to return to the matrix display.

You have a simple image, so the next step is to add the sparklines.

How to add sparklines to the visual in Power BI

With a simple visual matrix ready, adding the sparklines might seem like the easiest part. To do this, make sure to select the array if necessary, and in the Visualizations pane, find the Values ​​bucket and right-click Amount (Figure F).

Figure F

Add sparkline option in Microsoft Power BI
Figure F – Choose the Add Sparklines option.

In the resulting submenu, click Add Sparkline. In the resulting dialog, choose Date as the x-axis (Figure G). To do this, use the dropdown menu and Power BI will display the hierarchy of the table, so you can choose the correct field.

Figure G

Add a sparkline menu in Power BI with the Date field selected for the x-axis
Figure G – Choose the date/time field for sparklines.

Click Create to see the sparklines displayed in Figure H.

Figure H

sparkline trend data in Microsoft Power BI
Figure H: Sparklines show sales over time.

If you’re wondering about the choice of x-axis, remember that sparklines are a time element. You are looking at values ​​over time. This option will always be a time/date column. The date values ​​determine the time range.

The best place to find out more about that time is in the raw data, which you can see by clicking the Data icon on the left. as you can see in figure idates begin January 1, 2021 and continue through July 15, 2021.

figure i

Microsoft Power BI data with Quantity column selected
Figure I: The dates in the source data determine the time span.

As you expose more date information, as shown in J-figure, sparklines lose their usefulness; they have no meaning when it comes to representing single date values. For this reason, you may want to remove those layers at the bucket level, unless your end users are filtering by individual sales amount. However, if that’s the case, I’d suggest you create a second visualization for that.


sparklines that represent trends over time in Microsoft Power BI
Figure J: Sparklines are useful only when they represent trends over time.

How to interpret sparklines in Power BI

Sparklines expose information you might miss when looking at a spreadsheet:

  • The central region had a sharp drop in sales and appears to have no sales at the end of the period. That requires a closer look.
  • Sales for the Northeast region have decreased during the period. Once again, you’ll want to take a closer look at what’s going on.
  • The Northwest region had no sales at the beginning of the period, but had a surge at the end of the period.
  • Southeast sales trended up for a while, but then fell back.
  • Southwest sales trended lower but appear to be recovering.

stay tuned

We have successfully and quite easily added sparklines to a matrix visualization in Power BI. You can stop here, but stay tuned for a future article, where I’ll show you how to improve the default results with a bit of formatting.

Leave a Comment