Power BI Desktop: Custom Aggregations, Formatting and Performance Indicators (Part 2)

[Dianapo]

This multi-part tutorial blog is geared to help BI Analysts create versatile Scorecards with KPIs as well as to faciliate the business reporting transition from Excel, SSRS or other apps to Power BI Desktop.

In part 1 of this tutorial, I illustrated how to include more than one aggregation and custom formating in the Power BI Desktop Matrix. In this installment, I will give a step by step tutorial on how to add a color performance indicators next to the Actual value based on the associated Target value.

Note, we will be utilizing the PerformanceYTD table (attached), as well as the calculated measures created in part 1.

Add Color Indicators based on Performance Against Target

Adding color indicators next to Actuals can be accoplished in the following steps:

• Step 1: Click on the top right ellipses of the PerformanceYTD dataset and select New Measure to create a calculated ActualAgainstTarget measure:
• Step 2: Once you click on New Measure, paste over the following DAX Expression for the new ActualAgainstTarget calculated measure:

ActualAgainstTarget = IF(MAX('Measures YTD'[Below Target is Good]) = "Yes", [TargetWithAggregations]/[ActualWithAggregations], [ActualWithAggregations]/[TargetWithAggregations])

• Step 3: Click on New Measure again, and paste over the following DAX Expression for the new Indicator calculated measure:

Indicator = IF([ActualAgainstTarget] = 0, UNICHAR(128211), 
                  IF([ActualAgainstTarget] >= 0.95, UNICHAR(128215), 
                  IF([ActualAgainstTarget] < 0.75, UNICHAR(128213), 
                  IF([ActualAgainstTarget] >= 0.75, UNICHAR(128210), UNICHAR(128211)))))

Note, in the above DAX expresssion,  that if Actual is as of 95% of Target green indicator is assigned, and if Actual is less than 75% Target then red indicator is assigned, and lastly if the Actual is between 75% and 95% Target then yellow indicator is assigned.

• Step 4: Click on New Measure again, and paste over the following DAX Expression for the new ActualwithAggregationsAndFormattingAndIndicator calculated measure:

ActualwithAggregationsAndFormattingAndIndicator = IF([ActualWithAggregationsAndFormatting] > 0, FORMAT([ActualWithAggregationsAndFormatting] & " " & [Indicator], 0), 0)

• Step 5: Add the last calculated Measure, ActualwithAggregationsAndFormattingAndIndicator, in the Matrix Values section and rename it to Actual:Note, Edge, Chrome and most Browsers work great but IE is color blind to the UNICODE indicators and I thus do not recommend it. Also older versions of Windows may also show the indicators in grey. I am running Windows 10, version 1709.

  I hope you enjoyed this multi-part tutorial for adding Custom Aggregations, Formatting and Performance Indicators in the Power BI matrix.