Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Galleries
- Quick Measures Gallery
- SVG Sparklines - Categorical Columns

Highlighted
##

deldersveld

MVP

SVG Sparklines - Categorical Columns

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-14-2018
10:31 AM

Power BI now allows the dynamic creation of Scalable Vector Graphics (SVG) images. Prior to the August 2018 version, it was possible to build SVG content into custom columns, but now it is possible to create measures that dynamically update to account for user selections and filters.

For additional detail on the method, please see this post on the DataVeld blog: Use SVG Images in Power BI: Part 3

**Get the DAX: SVG Sparkline "Column" measure formula on GitHub**

Substitute your own measure in place of **[Measure Value],** a column for **'Table'[Customer Segment]** for the categorical X axis, and a column for **'Table'[Category]** that will appear on the same table row as the sparkline.

Since the x axis is categorical, it is built and sorted using RANKX. Bar Width is found by dividing 100 by the number of distinct categories, and columns are adjusted to the range 0-100 by multiplying Rank * Bar Width.

The y axis is complex because of the need to account for a mix of positive and negative values. If all Y values were positive, the axis could use **[Measure Value] / Max **and would be fairly simple. Instead, depending on the combination of positive only, negative only, and mixing negative and positive values, there are SWITCH( ) statements used for establishing both the SVG Y value and the Height of the bar.

CONCATENATEX combines the separate SVG rectangles.

**IF(HASONEVALUE('Table'[Category])[...]** helps hides the total in a Table or Matrix because the total renders as text and not an image.

Once the sparkline measure is ready, set the Data Category to **Image URL** and add the sparkline measure to a Table or Matrix visual.

In addition, change the BarColor variable from static to dynamic to create conditional color formatting. In this example, if the Margin % is not greater than 20%, the sparkline should be grey instead of green. Because Firefox will not render # in SVG, substitute %23 in place of # if using hex colors.

//Note: use %23 instead of # for Firefox compatibility

VAR BarColor = IF([Margin] > 0.20,"%2301B8AA","%23BBBBBB")

Create additional variables for stroke width or any applicable SVG property and subsitute new variables into the SVG text as needed.

eyJrIjoiM2ZjNTQ1OTItMmIyYi00ZWE5LTg3OWMtNjMxM2M5OWUzMzkxIiwidCI6ImFjYzhhYWE1LWYxOTEtNDgyZi05MjFiLWNmNmMzM2E1ODgzMiIsImMiOjF9

jeffreyweir

Helper III

Re: SVG Sparklines - Categorical Columns

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-06-2018
05:20 PM

Question: One thing that's not clear to me is whether the images these use have to sit in a square container in the grid. In the case above, if there were say 8 more series, meaning twelve columns in all, is it possible to have the resulting SVG -generated sparkline sitting in a rectangular grid instead of a square grid, so that the spacing between rows is exactly as it is in the file, while leaving the image at the same size as it currently is? i.e. like this:

deldersveld

MVP

Re: SVG Sparklines - Categorical Columns

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-07-2018
05:34 AM

The table and matrix visuals limit you to a square grid.

jeffreyweir

Helper III

Re: SVG Sparklines - Categorical Columns

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-07-2018
02:54 PM

Bugger!

My understanding is that MS could change this if they wanted...they just didn't think we'd be doing things like producing Sparklines using SVG measures.

Of course, what we really need MS to do is to implement sparklines natively in a Table/Matrix, as per this idea on the Ideas forum.

Preferably multi-series sparklines like I talk about at this comment on an Excel thread.

Drakow

Regular Visitor

Re: SVG Sparklines - Categorical Columns

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-09-2018
04:31 AM

Hello, I have noticed that if I filter the table to show only one record using a slicer visual, the graphic errors and I lose all other information within the table. Is there a way around this? It works well otherwise. On a separate note, would it be possible to show an example of using a pie chart instead of a bar graph?

Many thanks,

-Emesh

Kartic21

Frequent Visitor

Re: SVG Sparklines - Categorical Columns

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-01-2019
07:05 AM

Implemented this in PBI. Thanks much.

Have one question, is there a way to increase the size/width of the bar without increasing the height.

What is happening is the bar chart is appearing very tiny, there is a wide column width but the each bar width is very small.

Anyway to increase it ? Thanks in advance. Great Learning

jkdeepakkumar

Regular Visitor

Re: SVG Sparklines - Categorical Columns

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-20-2019
12:01 AM

Hi, Using this measure, is it possible to give color code per Customet Segment? like Consumer in black color / Corporate in red / Home office in amber / Small Business in green. If possible can you please help me on this senario.

thanks in advance!

jorge_oliveira

New Member

Re: SVG Sparklines - Categorical Columns

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-29-2020
04:25 AM

Hello,

Now that almost 2 years have passed, I would like to know if the question that @jeffreyweir made is already possible? Have a rectangular grid instead of a square grid?