Data Bear – Power BI Training and Consulting

Call us Today! (020) 8720 6880 | info@databear.com

Adding targets in Power BI report

Adding targets to Power BI Report

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.

Adding targets to Power BI Report

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.

Sales vs Target chart

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.DAX Calendar Table
  2. Target table  – consisting of the following fields:  Country, Month (Month End), Target AmountTarget table
  3. Country table – 1 column with list of countries

Country Table

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.

Correct format date

Set up the relationships as below.

Model view

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.

Sales vs Target

 

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:

DAX to check against target

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

Conditional formatting for data colors

The visual will then look like this.

Sales vs Target Colors

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.

Leave a Comment

Your email address will not be published. Required fields are marked *