Power BI Sparklines are a quick way to visualize time series data displayed in a Matrix or Table visualization. We’ll explain what they are, how to enable sparklines and when you may want to consider using small multiple visualizations as an alternative.
Sparklines in Power BI allow users to display small line graphs in columns next to numeric values or as rows or columns of their own visuals They’re a great way to compliment existing data visualizations.
Let’s jump in!
Table of Contents
Power BI Sparklines Explained
Sparklines first appeared in Excel 2010. They offered a quick way to visualize data trends directly in cells. Over the years, Excel expanded its sparkline functionality, allowing users to create line, column, and win/loss sparklines, before the feature was integrated with Microsoft Power BI.
Sparklines are small, simple, word-sized graphics, first coined by data visualization expert Edward Tufte. In Power BI, they serve as mini-charts embedded within tables or matrices.
The screenshot below shows a Power BI Sparkline added to a matrix visualization. It shows the trend of each amount shown in the column to the left of it as a trend over time.
Unlike standard charts, sparklines are designed to be compact and used inline with text or tables, making them ideal for dashboard displays. Sparklines are customizable allowing you to mix and match metrics with different X and Y axis in a visual.
When to Use Sparklines
Sparklines are particularly useful in scenarios where space is limited, and a quick visual of data is needed to either replace or compliment data that would normally be presented in a tabular format. They excel in showing data trends over time. However, because they’re so small and Power BI offers limited abilities to drill into them or view detailed information, they are really only suitable for a quick overview or a gut check of the data.
They can be helpful as a starting point to compliment with Power BI Drill Through capabilities. By using a drill through you can present small summaries of data using a sparkline and then enable users to click on a chart and drill into a more detailed view with either a larger chart or a full-featured dashboard with many additional visual elements.
How to Enable Sparklines in Power BI
To add a sparkline in Power BI, first add a Matrix or a Table visual to the Report View. Add desired rows, columns, and values. Then click the arrow for more options on the right side of a value, and select Add a Sparkline. A window will appear prompting you to choose the Y-axis, X-axis and aggregation method. When finished click Create and the sparkline will be added to the visual.
Here’s how in more detail.
Step 1. Setup a Table or Matrix Visual
Sparklines is that they are only available in the Table and Matrix visuals, so the first step is creating a standard table or Matrix. Fill out each of the Row, Column, and Values sections as needed.
In our example we will use a Matrix visual, like the one shown below.
It’s important to have something assigned to the Values section. You can always remove it at a later point.
Step 2. Add a Sparkline to the Table or Matrix Values
To add a sparkline, click on the down arrow to the right of one of the values assigned to your visual. This expands a contextual menu where you can add spark lines.
After selecting Add a Sparkline, Power BI will present you with a screen that lets you Add or Edit an existing sparkline.
Step 3. Assign X, Y values and Aggregation Method
In the sparkline editor, you are creating a chart that will be repeated based on the other fields assigned to a Matrix or Table. You have the option to choose fields that have already been assigned to the visual, or you can use ones that are different than what’s currently being presented.
Click OK once complete, and a new sparkline field will be created and visible in the Values section of the Matrix visual.
Note: If you choose to use fields that are not on the same table as the Matrix visual, you will need to ensure that appropriate relationships exist between the tables for it to display appropriately. Think of sparklines similar to how you would add a measure to a Matrix that must be calculated.
Step 4. Review Results and Notes on Limitations
After creating a sparkline, a new field is created under the values section of the visual. Sparklines are notated with a small graph icon to the left of the Value.
At this point we have successfully added sparklines to our chart, but there are several limitations and best practices that are helpful to be aware of.
- Sparklines are unique to the individual visualization. A reusable field is not created in the Data panel of Power BI.
- Sparklines act similar to other values in a Matrix meaning that they will automatically display grand totals or subtotals. These settings can be disabled under the “Format Your Visual” options.
- There are limited formatting options for Sparklines compared to other graph types and larger visuals, sparklines also lack the capability of conditional formatting.
How to Change Power BI Sparkline Formatting
To change the formatting of a sparkline, select the visual that has a sparkline assigned. Then navigate to the Format Your Visual options on the visualizations panel. It is noted by an icon of a paint brush in front of a chart. Sparklines are usually at the bottom of the list. Options include the ability to change chart type, color, width, and marker.
The following screenshot shows sparkline settings available.
If you have multiple sparklines on a visual, you can choose each sparkline separately and change the color or other formatting options independently.
Small Multiples as an Alternative to Sparklines
While sparklines are incredibly useful for embedding graphs into a Matrix and word sized visualizations, they can be more difficult to interpret due to their small size. An alternative worth considering can be using the Small Multiples functionality of Power BI.
Small multiples use fields, such as a category to automatically generate an individual chart for each category that exists within a dataset.
The example below shows a Matrix visual on the left with sparklines enabled, and the right side shows a line chart using small multiples to automatically generate an individual graph for each category in our dataset.
This is meant mostly as an illustration of what’s possible, when you deploy small multiples in real world scenarios you maintain all of the formatting options that you would normally have with varying chart types in Power BI. Though, they can be difficult to scale down to the size of a sparkline and align with a Matrix visual.
Sparklines vs. Small Multiples in Power BI
Here’s a quick rundown of the pros and cons of Sparklines vs Small Multiples.
|Miniature charts showing trends
|Series of similar graphs or charts using the same scale and axes
|Showing trends in a small space
|Comparing multiple datasets
|Compact, easy to read
|Detailed, allows comparison
|Requires more space
|Detailed analysis across categories
Between the two features there is generally a way to get to the desired result that you want allowing you to create multiple views, or multiple charts without manually creating and filtering each one.
Sparklines in Power BI offer a compact and efficient way to represent data trends and can be an extremely useful compliment to values displayed in a Power BI table or Matrix visual. You can add one quickly, by selecting the dropdown menu of an assigned value and select “Add Sparkline. A prompt will allow you to select an x-axis, y-axis and aggregation method.
Sparklines provide only limited formatted options. For more comprehensive capabilities, considering allowing users to drill through from a matrix visual to another tab of a report, or utilize the small multiples feature of a standard graph visual to automatically replicate charts without having to manually adjust each one individually.