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.

[Anonymous]

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:

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. 🙂 

[Dianapo]

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 🙂

[Anonymous]

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.

[Dianapo]

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.

[danextian]

Chrome running on Windows 7 is color blind.

But the icons look better on Firefox.

Chrome (Windows 10)Firefox (Windows 7)Looks way better in Firefox

[Anonymous]

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

[danextian]

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

[Anonymous]

Thanks for these blogs - they've been really useful!

I've pretty much followed them to get a calculated measure with different data types in, and based off of this created another calculated measure to get a Red/Amber/Green (RAG) status. 

What I'd now like to do is have a bar chart of counts for the RAG Status, but this isn't possible as it's a calculated measure not a calculated column - is there something simple here I'm missing to be able to make this work? I keep getting circular dependency errors when I try to create the RAG measure as a column...

I'm also concerned that if this sort of thing was calculated at row level it wouldn't be able to dynamically change based on aggregations? E.G. I collect data at department level, to present aggregated up at the overall business level, with the option for users to then see down to department level. If the RAG was a static calculated column would this present impossibilities?

[Dianapo]

Thanks @Anonymous and you can definitely create a column with the kpi color and count the measures and maybe one day I will get around to writing part 3 which addresses these extras.

This is the code for the KPIColumn to plug into your chart (which I just quickly wrote but haven't tested)

KPIColumn = IF([Target] = 0, "   Needs Target " & UNICHAR(128211), IF([ActualAgainstTarget] >= 0.95,  " Over 95% of Target " & UNICHAR(128215), IF([ActualAgainstTarget] < 0.75,  "  Below 75% of Target " & UNICHAR(128213), IF([ActualAgainstTarget] > 0.75, " 75% to 95% of Target " & UNICHAR(128210), "   Needs Target " & UNICHAR(128211)))))