How to add an Indicator to Power BI Desktop

Share this...

Twitter

Linkedin

Facebook

Pinterest

Reddit

Yesterday was an exciting day – Microsoft released the GA version of Power BI and with it comes an impressive list of new features and functionalities. One of the new features that I was really interested in was – Rich control over visual coloring, including conditional formatting in Reports. This opens up a whole new possibility of tweaking Power BI even when the feature is not available out of the box. Today’s blog is going to be an example of that! (NOTE: This workaround is needed to add an indicator on the version that was released on 7/24/2015. The Power BI team moves really FAST with updates and there is every possibility that some features which make this workaround redundant, might be added sooner than later.)

Adding a KPI to a card is easy and helpful. But what makes it more useful is if there is an indicator which gives an idea on how the KPI is performing. Right now, there is no way to add an indicator out of the box in Power BI. But thanks to the new features, we can implement a workaround which is not that perfect, but will work for now. The finished dataviz looks like below

Note that there are 3 features here that look like it is not possible out of the box:-

1) There is an up / down arrow which changes based on the Year over Year (YoY) change.

2) There is a bar indicator on the top of the arrow which changes color (red or green) based on YoY

3) The background for the entire KPI box has a custom color (Neither the card data visualization nor the text box has formatting capabilities). So how is this done? Follow the steps below:-

Up / Down Arrow

Currently, there is no way to add a dynamic image in Power BI. You can’t import a binary type image data into Power BI neither add the image URL. A static image will not do for our purpose as we want the arrow to change based on data.

1) Let’s say that I have a measure called YoY which is basically the difference of Current Year sightings and Previous Year UFO sightings. Now make a new measure called YoY change which is YoY Change = [YoY] & ”  ” &  IF(SUM([Sightings]) >= [Prevyear], “⇧”, “⇩”) Yes, the entire trick is based on using the Unicode characters for arrows as the indicator. Unicode characters will work without any issue and can be used in regular DAX measure expressions. The above measure will show an up arrow if the current year is greater than or equal to previous year, else down arrow. You can also explore the other Unicode characters if you want to look for alternative symbols.

2) Now we can just use this new measure in a card. The measure expression will ensure that the Up arrow or Down arrow gets displayed accordingly.

Bar Indicator with Color on top of Arrow

Currently, there is no way to format a text box or a card viz. Else that would have been a good way to achieve this functionality.

1) For this demo, I have made a table with 1 column called Meaning with 2 rows – Up and Down. And then I made a simple measure that displays 1 for Up if the current year is greater than or equal to previous year or 1 for Down if the reverse is true.

PosNeg = SUMX(VALUES(temp[Meaning]), IF([Meaning]=”Up” && SUM([Sightings]) >= [Prevyear] || [Meaning]=”Down” && SUM([Sightings]) < [Prevyear],1))

2) Make a bar chart out of the Meaning column and the new PosNeg measure.

3) Click on the Format tab (on the right hand side panel) and then ensure that all properties except Y axis has been turned off. Then expand the data colors and choose the default color a Green. You can also click on the Show All property and manually make sure that the Up value is Green. (If the default value for your data is Down, then make sure that the color is Red).

4) Now choose a filter condition which will show the bar chart for Down value. After that, go to the Format option and choose the color for Down as Red.

Power BI remembers that Up has a color of Green and Down has a color of Red. You can test it by toggling the filters / slicers.

5) Make sure the transparency of the background is set to 100% (don’t turn it off as shown in the image below) and also turn off the Y axis now. Resize the bar chart and place it above the previous card viz for Up / Down arrow indicator.

Now you have got a nice color indicator also!

Background for KPI Box

Right now, there is no way to add a background color to the KPI box, as we cannot format the card data viz or the textbox viz. But what we can o as a workaround is to select any column, make it into a bar chart and then turn off all the options so that we get a blank rectangle. Then go to the background option and choose the color of your choice.

Feel free to drop in the previous two indicators that we made into the colored rectangle and now we have got a much prettier and useful KPI box. There is a lot of different ways in which you can mix and match this technique. For example, how about using a bubble indicator instead of the bar?

Note that these dataviz can not be pinned to a Dashboard, as we can not overlay visualizations in a dashboard, However, these will work just fine if you upload it to a normal Power BI Report. Having the control over formatting has greatly increased the ways in which you can tweak the reports and I am loving it. Looking forward to what all surprises the Power BI team has for us now!