KPI Indicators Showing Trends in a PowerBI Table or Matrix

At ShareAdvance, helping companies better capture and understand their project data is our core mission.  Power BI has become our visualization tool of choice – the mix of capabilities and ease-of-use is unmatched.  The team at Microsoft is constantly adding new functions and we can’t wait for each new monthly update.  There are still many features we’d like to see; in the meantime, as part of the Power BI community we do our best to find creative ways to create needed reports and share when appropriate.  Here we show a solution for creating a KPI indicators visual showing changes over time.

Currently the only way to get KPI status icons for reporting in a PowerBI table or matrix is to import an Excel workbook that has a Power Pivot model with KPIs. That’s not always ideal so one work around is to use Image Url’s. Here I’ll explain how we did this to show not only the current status of a project, but also the ability to compare to previous status in one concise image.

First the data. We have multiple columns representing the status of different project categories. The raw data looks something like this:

Now on to creating KPI indicator icons.  Five minutes in photoshop and I have my 5 colored, 24×24 png files.

However, we want to display – in one indicator – the previous status and the current status so that the trend is clear. My initial thought was let the server generate dynamic images based on the url. But why tax the server for something so trivial?  With 5 colors there’s only n!/(n-r)! + n combinations if we allow each color to combine with itself.  In our case:

5!/(5-2)! + 5 = (5*4*3*2*1/3*2*1) + 5 = (120/6) + 5 = 20 + 5 = 25

Not wanting to create all 25 by hand, and assuming of course users will add colors later, I scripted it.  With ImageMagic installed it only takes a few lines of PowerShell.
# each image should be 24x24
# output images are 48x24

$colors = @("blue", "green", "grey", "red", "yellow")

function Combine-Images($left, $right)
{
    write-host "Combining $left and $right"
    & "magick" -size 48x24 "$left.png" "$right.png" +append "$left$right.png"
}

foreach($color in $colors)
{
    write-host $color
    foreach($color2 in $colors)
    {
        Combine-Images $color $color2
    }
}

This generated our 25 images that look like this:

I then combined two columns from a query that has a column for the previous status and a column for the current status to produce a combined value that looks like:

YellowBlue

Now all we have to do is format our data to look like Urls instead of the combined color names.

let
    VAR_Url = "http://cdn.shareadvance.com/images/kpi/",
    Source = ProjectHistory,
    Status = Table.AddColumn(Source, "Stat", each VAR_Url&[ProjectStatusPrevious]&[ProjectStatusCurrent]&".png")
in
    Status

Once the queries have columns displaying the image Url’s, I needed to let PowerBI know this so it will display them as images instead of text.  To do this go to Data, then Modeling:


Now I set the columns to “Image Url”. This is necessary or the columns will show the actual url text.

One caveat to this is if the column headers are different lengths, the images will be different sizes. Here was the table with the original column headers.

The easiest fix for this is simply renaming your column headers so they are all roughly the same width. I went with 4 characters which worked out nicely.

The final table looks much more interesting. Projects that don’t change will have a long block the same color.  Projects that have changed status from last week will show the multicolored blocks.

Until PowerBI adds better support for KPI indicators using Image Url’s is one way to go.

If, like us, you’d like to see this built into PowerBI, please vote on these ideas:

Ability to set goals and thresholds for KPIs

KPI Icon sets for use in table and matrix visualizations