How to add an Indicator to Power BI Desktop

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

PBI

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

image

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.

1 Power BI Indicator

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.

2 Power BI Indicator

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

3 Power BI Indicator

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.

4 Power BI Indicator

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 Power BI Indicator

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.

6 Power BI 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.

7 Power BI Indicator

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?

8 Power BI Indicator

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!

Posted by SQLJason

13 comments

Great post!!! Very useful. Thank you!

Very clever approach. Thank you!

Great Post very much useful!!!..:)

Fantastic post. Thank you!

[…] being shared on Twitter with hashtag #PowerBI by the rapidly growing Power BI community. From trend indicators with classic SSRS Unicode character tricks now being applied to Power BI Desktop in DAX, to […]

Hi Jason!
Thanks for the post, however, I’m facing some problems implementing what you illustrated.
1. Power BI is not accepting the up and down arrows as you pointed out. Please help.
2. When I type in the formula for PosNeg, it gives me an error saying “Invalid token, Line 1, Offset 53”
Where is the [Value_If_false] for the IF condition?
My formula is as below:
PosNeg = SUMX(VALUES(colorformatdata[Meaning]), IF([Meaning]=”Up” && SUM([Pilot_rpt7day])/SUM([Pilot_Calls]) SUM([Control_rpt7day])/SUM([Control_Calls]),1))

Hi Jason!
Thanks for the post. However, I’m facing some problems implementing this:
1. Power BI is not accepting the up and down arrows as you pointed out. Please help.
2. When I type in the formula for PosNeg, it gives me an error saying “Invalid token, Line 1, Offset 53”
Where is the [Value_If_false] for the IF condition?
My formula is as below:
PosNeg = SUMX(VALUES(colorformatdata[Meaning]), IF([Meaning]=”Up” && SUM([Pilot_rpt7day])/SUM([Pilot_Calls]) SUM([Control_rpt7day])/SUM([Control_Calls]),1))

sudhir prajapati

I am getting a error while concatenate UNICODE character with measure.

Hi Jason,

I would like to use this technique but my problem is I have to pin the card as a tile in a dashboard, and the users also want to see the colour indicator. Do you have new ideas on how to replicate the KPI visual (with different tick marks) of Power BI?

Regards,
Aleli

Can you please share the report as well as the sample data for the same ?

Hi Jason, Even though it is accepting my measure I get a message “The value for ‘Total Number of High Risk SP’ cannot be determined. Either ‘Total Number of High Risk SP’ doesn’t exist, or there is no current row for a column named ‘Total Number of High Risk SP’.

Any thoughts on this?

Thank you

This is an old article but really helped me a lot! Thank you! You would think by now, 3 years later, that Power BI would let you define KPIs within a data set like you can in tabular or Power Pivot, or at least allow conditional formatting on measures from a data set.

An indicator really isn’t useful unless it also indicates whether change is good/bad.

Leave a Reply

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