Showing KPI’s in a table or Matrix with Power BI

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] ) > 215SVG_UP,
        SUM ( Sales[Sales] ) < 50SVG_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.

7 Replies to “Showing KPI’s in a table or Matrix with Power BI

  1. 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.

      1. 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!

  2. 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.

  3. 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?

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.