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
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
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
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
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
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
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
Click Create to see the sparklines displayed in Figure H.
Figure H
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
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.
J-figure
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.
