Charts are a great visual tool for sharing information; they share your data history at a glance. For Power BI, rankings are everything and there are plenty to choose from. One of the easiest to create is visual brilliance.
Power BI embeds cell-level thumbnail charts in either the table or matrix. They are easy to add and very strong.
In this tutorial, I will show you how to add spark lines to a visual in Microsoft Power BI Desktop on Windows 10 64-bit system.
What are spark lines?
Sparklines are small charts that you embed in a data table. If you are familiar with Microsoft Excel, you may have already come across them. They have been around for a long time and are popular because they fit in a cell right next to the source data.
Viewers get a quick look at trends, growth, lows and highs, etc. They are easy to implement given the story they tell about your data.
Choose spark lines when you want to display the details stored in a table with a chart that displays minimal information. Normally, Power BI hides the X-axis, and given cell space limitations, you won’t use them for detailed analysis.
Before we can 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 data from Excel into Power BI
We will import the simple Excel sheet shown in Figure A. If you’re following along using the demo .xlsx file, save it to OneDrive or SharePoint and note the location.
To import this data, launch Power BI as you normally would and close the information window. In the default window, click the Excel icon next to Get Data in the menu. In the resulting Open dialog box, locate the demo file, PBIImportFromExcel_SourceFile.xlsx (Figure B), or the file you intend to use instead.
Click Open to import the data into Power BI. An Excel file has a sheet and a Table object. In the resulting navigator, check TableSales (Figure C) and then click Load.
With the data available in Power BI, let’s build a visual.
How to build the visual in Power BI
Power BI supports spark lines in table and matrix visuals, which makes sense because Power BI embeds small charts in cells.
To create a simple visual matrix, select Matrix from the Visualizations panel. Complete the matrix by dragging Region and Date to the Rows group and dragging Sum to the Values group.
Figure D shows the resulting matrix, which requires only a little explanation.
Expand any region in the matrix and you will find the dates grouped for that region (Figure E). Click the plus sign to the left of a region to display the first subset, which is the year.
Thanks to Power BI’s time intelligence and the underlying date table that Power BI creates automatically, you have several time components to filter.
If you’re not familiar with the date table, you can read later How to tell if an automatic date table is appropriate when using Power BI. 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.
Keep clicking the plus signs to show all time components for a specific region so you can see how much information is automatically in your visual. I also work in focus mode, which makes the visualization more readable.
Notice that I applied the currency format to the Amount column. You can do this in the Report window, but it’s much easier in the Data window. Using the previews panel, it’s multiple layers, 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 header, and then click the Currency ($) icon in the menu. Then click the report icon to return to the matrix preview.
You have a simple visualization, so the next step is to add the spark lines.
How to add sparklines to a visual in Power BI
With a simple die ready, adding the sparkly lines may seem like the easiest part. To do this, be sure to select the matrix if necessary, and in the Visualizations panel, find the Values group and right-click Sum (Figure F).
In the resulting submenu, click Add Sparkline. In the dialog box that appears, select date as the x-axis (Figure G). To do this, use the drop-down menu and Power BI will display the table hierarchy so you can select the correct field.
Click Create to see the spark lines shown in the Figure H.
If you’re wondering about choosing the x-axis, remember that sparklines are a time element. You are viewing values over time. This option will always be a time/date column. Date values specify the time range.
The best place to learn more about this range is the raw data, which you can view by clicking the Data icon on the left. As you can see in Figure Idates start January 1, 2021 and run through July 15, 2021.
While exposing more information about the date as shown in Figure J, the sparkling lines lose their usefulness; they don’t matter when trying to represent single date values. For this reason, you may want to remove these layers at the bucket level unless end users filter down to the individual sales amount. However, if this is the case, I suggest you create a second visualization for it.
How to interpret sparklines in Power BI
Sparklines reveal information you might miss looking at a spreadsheet:
- The central region had a major drop in sales and appears to have no sales for the end of the period. This requires a closer look.
- Sales for the North East region declined during the period. 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 sudden spike at the end of the period.
- Sales in the Southeast were up for a while, but then came back down.
- Sales in the Southwest have been trending down, but appear to be recovering.
Stay on the line
We have successfully and quite easily added spark lines to a matrix visualization in Power BI. You can stop there, but stay tuned for a future article where I’ll show you how to improve the default results with a little formatting.