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

by Dianapo Visitor on ‎04-03-2018 10:09 AM - last edited on ‎04-17-2018 04:30 PM by MindyJ Frequent Visitor

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.

 

Part2.JPGNote, 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:New Measure.JPG

     

  • 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:part2final.JPGNote, 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.

 

 

Comments
by ScottKaysee Frequent Visitor
‎04-05-2018 07:39 PM - edited ‎04-05-2018 09:08 PM

Hi again, your "Indicator" formula looks like it would miss values that = 0.75 and to simplify I would use the following:

 

Indicator = 
IF([ActualAgainstTarget] >= 0.95, UNICHAR(128215), /*>= 95% Green*/
	IF([ActualAgainstTarget] >= 0.75, UNICHAR(128210), /*>= 75% Yellow, but anything >= 95% caught in above condition*/		
		IF([ActualAgainstTarget] > 0, UNICHAR(128213), UNICHAR(128211)) /*>= 0% Red, but anything >= 75% caught in above conditions. Plus the fail/catch-all condition.*/
	)
)

Also using those UNICHAR codes appear as grayscale icons for me (in Australia), here is an example:

 

 

image.png

As seen in the list here: Using Unicode Character Symbols in Excel

 

However, that doesn't detract from the concept you're showcasing. Those codes can be set to the users desire.

Cheers again. Smiley Happy 

by Dianapo Visitor
on ‎04-06-2018 08:23 AM

ScottKaysee  at the end of the article I have posted a note that IE is color blind to the indicators (copy paste below)

 

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, yes I am missing one = sign there, but it is easy to catch and correct (I left it as is,  as it is a bit of a lengthy  process to redact a blog and the focus is more on how to add the indicators). When using Chrome, Edge, Firefox (at least these are the one I tested with on my end) the color indicators show as expected. Hopefully, this is also the same in Australia Smiley Happy

by ScottKaysee Frequent Visitor
on ‎04-08-2018 06:30 PM

Dianapo... RE: the indicators, I meant to add that the icons appeared that way for me in PBI Desktop and in Chrome (on a Windows 7 64 bit OS). Again, this is simply an FYI and doesn't detract from your blog post. Cheers and GL.

by Dianapo Visitor
on ‎04-10-2018 10:09 AM

Thanks ScottKaysee, and I will add this specific info as a note to the article once I get around to updating it (most likely this month) to make aware other users have this version.

by danextian New Contributor
on ‎04-18-2018 07:01 PM

Chrome running on Windows 7 is color blind.

But the icons look better on Firefox.

 

unichar chrome.pngChrome (Windows 10)unichar firefox.pngFirefox (Windows 7)Looks way better in Firefox

by ScottKaysee Frequent Visitor
‎04-18-2018 07:06 PM - edited ‎04-18-2018 07:06 PM

Thanks danextian... unfortunately I'm running on a locked down, corporate SoE. On the plus side, we should be moving to W10 "soon". Smiley Happy

by danextian New Contributor
on ‎04-18-2018 07:11 PM

Most of us are still running on Windows 7 which are not locked down but  will be once moved to Windows 10 Smiley Sad . I'd like  to use  the colored unicode characters but not until everyone's been moved.