One of the most common questions that gets posted in forums is, how to dynamically display images based on the values of the measures or how to use image as trend indicators for KPI. Using images in Power BI report is not so difficult if it is going to be a static one but, the challenge is only when we need to dynamically display the images based on the value of the measure.
For example, if the sales of a company have exceeded or met the target value, then a trend-up image should be displayed, if not a trend-down image.
Power BI currently supports conditional formatting of numeric values on scorecards and tables, but it does not support conditional formatting of image URLs.
However, we could accomplish this requirement manually by using custom images and a little workaround. The approach that I followed is three-folded as explained below:
- Set up the data model
- Creation of Measures
- Dynamically display the image
1. Set up the data model
Initially, a sample dataset is loaded into the Power BI desktop.
A calculated column could be added to the above dataset to load the image URLs. But if you notice in the data set, the sales data is flattened out and can be aggregated at different levels like Country, Segment, Product. By design the addition of a calculated column would lead to static value as the image URLs gets preloaded while adding the column itself and a URL gets populated for every row in the data set. And it won’t be possible to dynamically display the image if we roll up or roll down the data hierarchy. Hence added a separate table for the images to cater the requirement.
Once the data is loaded the data category for the URL column has been updated to Image URL type.
If you miss this step, Power BI would consider the URLs as mere text data.
2. Create Measures
The next step is to create the measures which need to have the conditional formatting on. For the example that I have taken, I’ve created a simple measure that is the aggregate value of the gross sales of the company and included the same measure (Total Sales) to the card visual.
To add a little more complexity to the business case, I have included a country slicer to see if the measure gets aggregated based on the slicer.
Let’s try playing around with a slicer to see if it’s working as expected.
Initially, Canada is selected on the slicer.
Now let us try to change it to Mexico and see if the value gets filtered for Mexico alone.
As expected, the score card reflected the sales based on the selected country.
3. Dynamically Display Image
Now comes the final and the most challenging part of the requirement.
How to dynamically display the image based on total sales data?
Say for example: A trend-up image to be displayed when the total sales have exceeded the target of 3.5M and a trend-down otherwise.
I have the measure created for total sales and now we need to figure out a logic to filter the images based on the condition.
It is not possible to append the image URL to the filter condition as shown below, as it would change the context and the URLs would be taken as text values.
IF (Total Sales >3500000, <>, <>)
A separate measure, dedicated to filter the images based on the condition is required to address this scenario. Hence, I have added a measure Flag.
The measure Flag returns the data as shown below when displayed on a table:
After filtering the data based on the condition Flag=1 you will get the desired result.
I have used the custom visual Image by CloudScope for displaying the image in the report. And in the visual filters pane included the condition Flag=1.
You could now see that the image is displayed dynamically and shows a trend up for Canada where sales > 3500000 and trend down for Mexico where sales< 3500000
We could dynamically display the images based on conditions in a similar manner, even in a table. However, we cannot have control on the size of the image within the table.
We could also set up different targets at lower levels and can use an entirely different set of image indicators like
1) RED and GREEN lights
2) Thumbs-Up and Thumbs-down arrows
Adding too much numerical information in dashboards makes it’s difficult to read and get the insights. But with the image indicators the business users can easily get the state of Key Performance Indicator (KPI) at a glance and with this workaround explained above you could also achieve it in your Power BI reports.
Got questions? Click here to get in touch.