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:
Further Reading
o Adam Saxton YouTube Video – https://www.youtube.com/watch?v=821o0-eVBXo
o Power BI Blog – http://bit.ly/2kudZ0a
Contact Me
If you would like a copy of the workbook or have any questions about this blog, please leave a comment below.
Twitter: @DataVizWhizz
How Artificial Intelligence and Data Add Value to Businesses
Knowledge is power. And the data that you collect in the course of your business
May
Databricks Vs Synapse Spark Pools – What, When and Where?
Databricks or Synapse seems to be the question on everyone’s lips, whether its people asking
1 Comment
May
Power BI to Power AI – Part 2
This post is the second part of a blog series on the AI features of
Apr
Geospatial Sample architecture overview
The first blog ‘Part 1 – Introduction to Geospatial data’ gave an overview into geospatial
Apr
Data Lakehouses for Dummies
When we are thinking about data platforms, there are many different services and architectures that
Apr
Enable Smart Facility Management with Azure Digital Twins
Before I started writing this blog, I went to Google and searched for the keywords
Apr
Migrating On-Prem SSIS workload to Azure
Goal of this blog There can be scenario where organization wants to migrate there existing
Mar
Send B2B data with Azure Logic Apps and Enterprise Integration Pack
After creating an integration account that has partners and agreements, we are ready to create
Mar