Got a question today on how we can do KPI’s in the matrix or table with Power BI, just like we can with PowerPivot through the KPI functionality there.
This is where the great SVG trick from David Eldersveld comes into play, you can read all about it here in his blog posts: https://dataveld.com/2018/01/13/use-svg-images-in-power-bi-part-1 and this post by Chris Webb with even more cool examples.
In this case I am using his trick and a post I found on the community site to add a KPI to my table. First I created a table in Power BI that contains values and labels:
Next I create the KPI measure that renders and shows KPI’s based on the value in the table:
KPI =
VAR MessageText = “”
VAR Radius = 9
VAR Colour = “blue”
VAR Opacity = 0.75
VAR SVG_Neutral = “data:image/svg+xml;utf8,”
& “<svg xmlns=’http://www.w3.org/2000/svg’ x=’0px’ y=’0px’ width=’50’ height=’20’>”
& “<circle cx=’25’ cy=’10’ r='”
& Radius
& “‘ fill='”
& Colour
& “‘ fill-opacity='”
& Opacity
& “‘ />”
& “</svg>”
VAR SVG_UP = “data:image/svg+xml;utf8,”
& “<svg xmlns=’http://www.w3.org/2000/svg’ x=’0px’ y=’0px’ width=’50’ height=’20’>”
& “<polygon points=””05,20 45,20 25,1″” style=””fill:green;stroke:green;stroke-width:0;fill-rule:evenodd;”” />”
& “</svg>”
VAR SVG_DN = “data:image/svg+xml;utf8,”
& “<svg xmlns=’http://www.w3.org/2000/svg’ x=’0px’ y=’0px’ width=’50’ height=’20’>”
& “<polygon points=””05,1 45,1 25,20″” style=””fill:red;stroke:red;stroke-width:0;fill-rule:evenodd;”” />”
& “</svg>”
RETURN
SWITCH (
TRUE (),
SUM ( Sales[Sales] ) > 215, SVG_UP,
SUM ( Sales[Sales] ) < 50, SVG_DN,
SVG_Neutral
)
That’s it, now all I have to do is set the measure to be IMG URL
And now it renders KPI’s based on the values directly in my matrix:
The great thing here is that you can customize it anyway you want as David will explain in his blog series. For example look at what he did to add sparklines using the same technique:
https://twitter.com/dataveld/status/1027735453162717184
very cool!
Finally you can download my sample workbook here.
Super blog … but you have to ask how many BI practitioners could write such a technical KPI measure? Even after downloading the pbix, trying to change the size or any other aspect would be a cumbersome operation.
It just highlights the lack of easily usable KPI functionality in Power BI. I suspect (and hope) the Power BI team are taking note.
Hi Ted, of course you are right. You will see some of these will be added to the UI over time.
Thanks,
Kasper
That’s why advanced Power BI developers are in demand! Anyone can wait 6-9 months for Microsoft to develop this in the UI, but not all businesses want to wait that long. They want the advanced users to come up with these solutions and implement them now.
This has happened time and again. SELECTEDVALUE() is a very recent formula, but the pattern that preceded it:
IF( HASONEVALUE( Table[Column] ), VALUES( Table[Column] ) )
was around for years.
Microsoft depends on the advanced users to come up with workarounds, to be the driving voice in what the product needs.
In the meantime, kudos to the trailblazing bloggers who discover and share these tricks!
Thanks for sharing, exactly what I was looking for
I created a table the same as yours and tried to create the KPI measure but I get the following error:
The following syntax error occurred during parsing: Invalid toke, Line 2, Offset 19,”.
Any idea what could be wrong? I would appreciate your help. Thanks.
ill see what I can find. Haven’t seen this one, are you on the latest Power BI Desktop?
I think I had similar issues, was related to the quotation marks being formatted to a different type of quotation mark than that in Power BI.
Try this: https://paste.ee/p/QL0Cc
Super helpful. I have been using UNICHAR(128315) and UNICHAR(9650) for KPI’s so far, but SVG is more flexible. The only problem I have is when I activate Totals, then the code is shown instead of the image. Any idea how to fix it?
Hi,
Is there anyway to minimize the SVG icon?
I find that that it doesn’t minimize itself effectively. I’ve tried reducing the DAX text size and that doesn’t seem to work.
Thanks
Sam
you can generate your svg’s here: https://icons8.com/icons/set/arrow
once you select an icon then change change colour and then select html version you will get svg to use.
@Kasper, any soltuion with the svg under matrix roll up? Pawell also raised this issue