Data Bear’s Monday Motivation Blog Post

Today I will be showing you the dilemma I had about adding targets in Power BI for KPI’s in my dashboards and how it was approached.

My scenario was this.  I had Sales data over different countries and monthly targets for each are set and should be added to the dashboards to track progress and results.  The sales data has a more granular view than just by country, therefore I could not add the target data to the same dataset.

My targets are in another table in the database and get updated monthly, therefore I had to find a why of adding target in Power BI by using a relationship between the datasets.

The end result I was going for was a bar graph, showing Sales to date (or sales for the previous month) against the corresponding months target.  Noting that this was built to show the sales data on a monthly level.

This is the end result.

Tables that needs to set up for adding targets in Power BI

1. Date table (which is best practice for all Power BI models). I prefer a DAX calendar table but there are many ways to add one to your model Radacad has blogged on different options.
2. Target table  – consisting of the following fields:  Country, Month (Month End), Target Amount
3. Country table – 1 column with list of countries

Make sure the fields are in the correct format, because it may return confusing or blank visuals if relationships are not on the same data types.

Set up the relationships as below.

Relationships for adding targets in Power BI

Date table has relationship to Salestable (Date to Date) and Targets (Date to month)

Country table has a relationship to Salestable and Targets (country to country)

Then set up some measure for the Sales and Target totals.  These are just SUM() measures in DAX.

Visual Option 1

The visual I chose was Clustered Bar Chart and added Country (From country table) to small multiples section of the visual.  Use the date from the date table.

Visual Option 2

You can also use this model to color code Sales to show whether you reached target or not, without showing target on graph.  You can then add the target in the tooltip, because it gives the users some extra information.

Below is the DAX to check if sales is above or below target.  You can also use die DIVIDE DAX measure:

Set up conditional formatting on data colors option of the visual.

The visual will then look like this.

I hope this helps you with adding targets in Power BI.  I could not find anything specific to my issue, but there are many other blogs that address showing targets in KPI’s.  Curbal.com has a video on Goals so does BI Elite.  Also read my prior blog about KPI cards.  You can also add custom tooltips.

Our training option here.