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.
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:
I hope you enjoyed this multi-part tutorial for adding Custom Aggregations, Formatting and Performance Indicators in the Power BI matrix.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.