If you are a user of Power BI, you have probably come across the KPI visual. I am often frustrated due to the visual’s lack of flexibility. Also, I would like the old school KPI from Excel to be incorporated into tables and other structures. However, at the moment, these are not native to Power BI. So, there is a way to create a custom KPI based on a few extra steps. The most important part of KPI visual is the indicator and the color. Essentially, you can add the indicator with an arrow or a symbol. Additionally, you can use an image can be added with green and red indicator that you can import from a website.
The first thing you need to do is create a variance or conditional to be able to switch between the indicators. For example, the difference between this year’s sales and last years sales. The variance metric will be used to base your conditional. The goal will be to say if the variance is positive we would like to have a positive indicator such as ↑ and a negative indicator such as ↓
A simpler version would have a static number to base your conditional.
You can use any ASCII symbol from Excel or Microsoft Word symbol library or any ASCII symbol. The arrow can be found in the Cambria Math. You can simply copy and paste this into your Power BI calculations.
This symbol library will give you the indicators that you are looking for. You are not limited to arrows, you can use any symbol or logic you prefer. This result can be added to the matric visual or table visuals. However, this does not give you the full KPI visual. The best way is to concatenate and visuals.
You can add you metric next to the indicator with the formula below.
Session KPI = SUM([total sessions)&IF([Variance]>0,"↑","↓")
Alternatively, depending on the structure of your data, you can build a custom column with variance. However, you are not limited by variance as a KPI measure. You can create your own threshold. Say any visits to the website that are greater than 50 is considered a win. So you can create a threshold to of 50. In the formula below, I wanted to add the metric and the indicator.
Session KPI = SUM([total sessions])&IF([total]>50,"↑","↓")
You can also create a similar visual by simply overlapping the line graph or area graph with your KPI visual.
Now simply overlap the images
Using Images for KPI Icons
The key to using a KPI icon is using images that are hosted online. If you can host these on your own website this is best. However, you can use any web address. You can use a website like iconfinder.com to find the indicators that you are looking for. First, you going to create an additional column to your table, you can create a conditional column in the query editor. For simplicity sake, let’s use our previous example of anything over or under 50. So, If sessions are greater than 1 then the output is 1 otherwise 2.
Build a Corresponding KPI Table
You can build a simple table by entering data with the Enter Data tab, You can set 1 to correspond the link for the green arrow and 2 to link to the red arrow. You will need to then connect these tables. This can be done by merging the two tables in the Query Editor or by simply connecting the tables in the Table view.
You will need to add actual image link for your indincator into a table similar to below. Also you will to ensure that the link is set to image link as a data type.
Once your KPI table is built, the simplest way to connect the two tables is to connect them in the Table View. You can see that the Index column matches to the KPI column in the All Website Data table.
Once you have connected the tables, you will need to change the data type to Image URL as a data type.
Now that you have made these steps, you will be able to use the images as URL indicators in a table similar to what you see in Excel.