Data Visualisation

Dual KPI Custom Visual in Power BI

Posted on 10th February 20172nd April 2020 by Callum Green

10
Feb

On February 8th, Power BI released a new custom visual called Dual KPI. The purpose of this chart is to visualise two measures over time and show their trend based on a joint timeline. The absolute values may use different scales e.g. Sales and Profit.

This blog will not only show you how to set up the new visual, but also demonstrate how changing some of the settings can enhance a report. Adam Saxton posted a YouTube video that also walks through the Dual KPI.

Pre Requisites

In order to follow my example, you will need a copy of AdventureWorksDW2014 database – found here. You will also need to download the following custom visuals:

o Hierarchy Slicer – http://bit.ly/2kAv4Id

o Dual KPI – http://bit.ly/2l1qCTp

NOTE: This article assumes previous knowledge of downloading and importing Custom Visuals into Power BI Desktop. If this concept is new to you, Scott Murray’s blog gives great step by step instructions.

Prepare Data

Open Power BI Desktop and Get Data. Point to the new AdventureWorksDW2014 database and drop down Advanced Options. Paste in the following T-SQL:

SELECT

DPC.EnglishProductCategoryName

,DPS.EnglishProductSubCategoryName

,DP.EnglishProductName

,SUM([TotalProductCost]) AS [TotalProductCost]

,SUM([SalesAmount]) AS [SalesAmount]

,SUM([SalesAmount]) – SUM([TotalProductCost]) As ProfitAmount

,[ShipDate]

FROM [AdventureWorksDW2014].[dbo].[FactInternetSales] FI

INNER JOIN

[dbo].[DimProduct] DP

ON DP.ProductKey = FI.ProductKey

INNER JOIN

[dbo].[DimProductSubcategory] DPS

ON DPS.ProductSubcategoryKey = DP.ProductSubcategoryKey

INNER JOIN

[dbo].[DimProductcategory] DPC

ON DPS.ProductcategoryKey = DPC.ProductcategoryKey

WHERE ShipDate BETWEEN ‘2013-01-01’ AND ‘2013-06-30’

GROUP BY

DPC.EnglishProductCategoryName

,DPS.EnglishProductSubCategoryName

,DP.EnglishProductName

,[ShipDate]

When happy, click ‘OK’ to continue. The preview of the data will open. Click Load, as we do not need to edit any data in the Query Editor. Apply and changes and rename the query to ‘Internet Sales’ – final output below:

Some measures and attributes need to be formatted within the ‘Modeling’ Tab.

o ‘ShipDate’ = dd MMMM yyyy

o ‘ProfitAmout’ = Currency

o ‘SalesAmount’ = Currency

The final formatting step is to create a Product hierarchy, based on the three product attributes. Navigate to the Data tab, right click on the ‘EnglishProductCategoryName’ attribute and select ‘New Hierarchy’. Drag the attributes into the hierarchy and name it ‘Products’. It should look like the following:

Create Report Visual

We need to use both the Slicer and Dual KPI custom visual. To achieve this, follow the steps below:

Select the Hierarchy Slicer in the Visualizations menu and drag the ‘Products’ hierarchy on to the Fields box. The slicer will now appear in the report.

Select the Dual KPI Slicer in the Visualizations menu and drag the following measures to the appropriate chart properties box:

a. ‘ShipDate’ > Axis

b. ‘SalesAmount’ > Top values

c. ‘ProfitAmount’ > Bottom values

The chart is now configured and each metric/visual is explained in more detail below. Only the top KPI (Sales Amount) is shown because both use the same calculations.

1. This is a fixed growth percentage, comparing the last (06/30/2013) vs. first (01/01/2013) data point on the graph. The metric acts as a static KPI.

2. The Sales Amount value for the last data point on the graph. Also a static KPI.

3. The data point currently being hovered over. This dynamically changes when you move along the axes.

4. The Sales Amount value for the current data point being hovered over. Also dynamic.

5. % since metric that looks at the Sales Amount for the last data point on the graph and works out the growth based on the current data point being hovered over. To use the example in the screenshot:

– Sales Amount for 06/30/2013 = 51,596

– Sales Amount for 05/17/2013 = 18,442

– % since: ((51,596 – 18,442) / 18,442) * 100 = 179.7%

Enhancing the Report

As with all custom visual in Power BI, there are lots of settings that you may never use. I have picked out some that enrich the capabilities of the Dual KPI Chart:

o Fields

o Warning State

§ Set alerts around data freshness and view warning messages.

o Top/Bottom % change start date

§ For the fixed +/- % change on the chart, you can add an override start date. The dates could vary by product category and dynamically impact the % in the visual.

o Format

o Dual KPI Properties

§ Show abbreviated values, define multiple tooltips and show stale data warnings.

o Dual KPI Chart Type

§ Choice of either Area or Line charts.

I have applied the Top/Bottom % change start date functionality and also formatted the chart properties. The report now looks a little more professional: