Multiple Sparklines

Power BI Custom Visual
Resize Row Height by click and drag
Re Arrange Charts easily

CONTENTS

In this article

DOWNLOAD

Download sample pbix file

You can download this visual from Microsoft’s AppSource. Go to Visual’s page

OVERVIEW

It is difficult to see trends or patterns when you are presented with a table full of rows and columns of numbers. A graph – like a picture – can be worth a thousand words when it comes to visually representing data

A sparkline (line chart, column chart etc.) is a tiny chart in a Table cell that provides a visual representation of data for a particular category

This Power BI Custom Visual transforms your Table numbers into stunning charts to give more insights into your data.. The visual supports following chart types

  • Line Chart / Area Chart
  • Column Chart
  • Bubble Chart
  • Donut Chart
  • Bullet/ Bar chart
  • Normal values (text, number, image urls, web urls, UNICODES etc)

HOW TO USE THIS VISUAL

For better understanding on how to use this visual, download the sample pbix file by clicking here

This visual requires one “Category Field”. Sparkline charts are created across each item in Category Field.

The “Axis Field” is needed for “Line Charts” and “Column Charts”

Here is simple data table without “Axis field”. Copy the data to Power BI desktop and drag the fields as shown in picture.

CategoryValue1Value2URLDonutBubbleBarBullet
Microsoft-200-35https://pbs.twimg.com/profile_images/1221837516816306177/_Ld4un5A_400x400.jpg0.4293181.213.2|11
Amazon137-198https://pbs.twimg.com/profile_images/669103856106668033/UF3cgUk4_400x400.jpg0.668220184.816.8|14
Alibaba27-79https://pbs.twimg.com/profile_images/1239337249788985344/kCtYRfmZ_400x400.jpg0.402200310.8111.6|93
Google-12118https://pbs.twimg.com/profile_images/864282616597405701/M-FEJMZ0_400x400.jpg0.475386320.427.6|23

You should see a visual like below

RESIZE COLUMN WIDTH and ROW HEIGHT by holding and dragging mouse
You can hold and drag vertical gridlines to resize column width and horizontal gridlines to resize row height

REARRANGE /MOVE THE CHARTS/COLUMN FIELDS

To rearrange the charts/column fields, use the arrows on top of each Column. This is depicted below

To add line chart and column chart we need axis field. Lets add another table (Many side table) linked to above table by “Category Field”

CategoryAxisLine1Line2
MicrosoftJan-277
MicrosoftFeb-11-3
MicrosoftMar6100
MicrosoftApr-8779
MicrosoftMay-1569
MicrosoftJun6587
AmazonJan-98-62
AmazonFeb-273
AmazonMar5619
AmazonApr-4291
AmazonMay-8379
AlibabaJan-10-80
AlibabaFeb49-43
AlibabaMar-94-60
AlibabaApr-5130
AlibabaMay84-99
GoogleJan1444
GoogleFeb89-2
GoogleMar1-61
GoogleApr95-66
GoogleMay17-49
GoogleJun-78-69

Link the 2 tables using the category field

Now you drag and drop the Axis and Line Columns from the Many side table to get Line Chart and Column Charts in Cells. This is depicted below

FORMATTING OPTIONS

In this section we will cover each individual chart’s requirements and formatting options in depth

When you add more than one field for an individual chart, you need to choose it before formatting. Go to formatting pane>>Select Column for formatting

Now you can choose formatting options such as color, font size etc for your selected column/field.

The formatting options for each individual chart are explained below

a) Line / Area Chart

You can add as many line charts as you like and format them from Formatting pane>>Sparklines

1. Line Color and Line Type

2. Line thickness

3. Shade area and Area Opacity

This converts line chart to Area Chart

4. Add data labels

5. Show markers

6. Add vertical and horizontal padding to line charts

7. Add Reference line

With line charts, you can add a reference line(average of values, first value, last value , zero). Also you can conditionally format the line area above or below reference line

8. Same Y-Axis

By default, each line chart in a cell has its own Y-Axis. You can turn on the same Y-Axis option, if you want to have same Y-Axis for all the sparklines in a Column/ Field.

When your objective is primarily to display patterns and trends, it is recommended to turn off the Same-Y Axis option. However if you are interested in displaying the magnitude across categories, this option should be turned on

b) COLUMN CHART

Column Chart features are quite similar to Line Chart explained above. Please see demo pbix file for illustration

You can apply following formattings to a Column Chart

  • Bar Color for positive and negative bars
  • Border
  • Data Labels
  • Padding
  • etc

Its pretty intuitive, so please try it out

c) BULLET / BAR CHART

You can create a simple bullet chart showing a bar and a vertical line as target. You need to have 2 columns (bar values and target values) against each category.

Now add a calculated column as “Bullet=[bar column]+”|”+[target column and drop this calculated column to Bullet/Bar field. Please see the demo pbix file to see how it works.

You can add data labels, change colors, padding etc from the formatting pane

You can also do conditional formatting of the Bullet chart to show the variance between Bar and Target. This is depicted below

d) BUBBLE CHART

Details will be added in the webpage soon. Please see demo pbix file for illustration

e) DONUT CHART

Details will be added in the webpage soon. Please see demo pbix file for illustration

f) VALUES/TEXT/IMAGE URL/WEB URL

More details will be added in the webpage soon. Please see demo pbix file for illustration

PURCHASE PREMIUM VERSION

To use the visual without Watermark in Power BI Online, please purchase the Visual’s license for a 1 year period. The term “user” below includes both Report Developers and Report viewers.


License for




New Features in Version 4.0.1 of Multiple Sparklines

  1. Combo Chart

Now you can add a line plus column chart or a double line chart as a sparkline

Simply create a calculated column or a measure by combining 2 separate fields and drop it to the Combo Chart field. This is shown below. Please see the demo pbix file for illustration.

2. Global Y axis

Now you can choose Global Y Axis for trend charts like Line, Column and Combo Charts etc.

FREQUENTLY ASKED QUESTIONS

1) Why is my measure not returning correct value? The value appears to be AVERAGE of all AXIS values rather than SUM or vice versa

Answer: Please set the aggregation method in “FORMAT” pane instead of “FIELDS” pane for all sections which do not require AXIS field such as VALUES, DONUTS, BULLETS, BUBBLE, WAFFLE etc. This is depicted below

2) I cannot see the format section for “Line Chart”, “Column Chart” etc. despite having fields in this section

Answer: Please check the “Select Column for formatting” section. If the selected number for a section is more than the total number of fields in that section, the formatting option will disappear. Select 1 from the drop down and the Formatting option will reappear. This is shown below

If you have any questions you can reach me by sending an email at zubair@excelnaccess.com

If you have any constructive feedback, new ideas or have found bugs in the visual, please let me know. I would be so grateful to you


Share This:

Leave a Reply

Your email address will not be published. Required fields are marked *