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

[Dianapo]

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

In this initial installment, I will illustrate how to include more than one aggregation and custom formatting in the Power BI Desktop Matrix. All tutorial steps below require acquaintance with how Power BI Desktop works as well as basic to advanced DAX skills.

Let’s start by creating the initial Power BI Desktop matrix based on sample monthly KPI data that requires different YTD aggregations and formatting for the Targets and the Actuals.

PerformanceYTD table 

Note, to simplify this tutorial I am including all the data in one table. In production scenarios, I recommend normalizing it .

 Create the initial Power BI Desktop Matrix:

• Step 1: Ensure that you have the PerformanceYTD table above imported in your Power BI Desktop report (note, the Excel workbook with the PerformanceYTD data is included in this article)

• Step 2: Click the Power BI Matrix visual and drag and drop the KPI field in Rows, and Target and Actual in Values.

• Step 3: Go to the Matrix’s Format section and disable the Subtotals:

By default, the Power BI Desktop Matrix has the same aggregation and formatting applied to all KPI’s Targets and Actuals. I am going to add customization using DAX in the next two sections to meet the Performance YTD matrix requirements specified in the Aggregation and Formatting fields.

Add Custom Aggregation for the Targets and Actuals

Referring to the PerformanceYTD table above: Target and Actual values for the Avg Orders per Customer KPI should be averaged, Target and Actual values for the Total Orders and Total Customers KPIs should be summed, and the rest should always show the latest CalendarYearMonth's value. This can be accomplished with the following steps:

• Step 1: Click on the top right ellipses of the PerformanceYTD dataset and select New Measure to create calculated measures for the Target and the Actual:

• Step 2: Once you click on New Measure, paste over the following DAX Expression for the new TargetWithAggregations calculated measure:

TargetWithAggregations = 
IF(MIN('Measures YTD'[Aggregation]) = "Avg", 
AVERAGE('Measures YTD'[Target]),
              IF(MIN('Measures YTD'[Aggregation]) = "Last",   
CALCULATE (SUM ('Measures YTD'[Target] ),
                                   LASTNONBLANK ( 'Measures YTD'[CalendarYearMonth],
                                   CALCULATE (SUM('Measures YTD'[Target]) ) ) ) , 
IF(MIN('Measures YTD'[Aggregation]) = "Sum", 
SUM('Measures YTD'[Target]), 0)))

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

ActualWithAggregations = 
IF(MIN('Measures YTD'[Aggregation]) = "Avg", 
AVERAGE('Measures YTD'[Actual]),
              IF(MIN('Measures YTD'[Aggregation]) = "Last",   
CALCULATE (SUM ('Measures YTD'[Actual] ),
                                   LASTNONBLANK ( 'Measures YTD'[CalendarYearMonth],
                                   CALCULATE (SUM('Measures YTD'[Actual]) ) ) ) , 
IF(MIN('Measures YTD'[Aggregation]) = "Sum", 
SUM('Measures YTD'[Actual]), 0)))

• Step 4: Add TargetWithAggregations and ActualWithAggregations to the Matrix Values section. Note the aggregation differences before removing the initial Target and Actual fields from the Matrix Values section.

Next, we are going to add custom formatting to the Target and Actual values.

Adding Custom Formatting for the Targets and Actuals

When measures of different nature are tracked in the same Matrix, custom formatting may be necessary. In our case, we need four different custom formatting types ranging from Percent to USD Currency. This can be accomplished with the following steps, similar to the previous section:

• Step 1: Click on the top right ellipses of the PerformanceYTD dataset to create another set of calculated measures for both the Target and the Actual fields, by selecting New Measure for each.

• Step 2: Use the following DAX Expressions for the new TargetWithAggregationsAndFormatting calculated measure. Note, it is computed based on the TargetWithAggregations measure created in the previous section.

TargetWithAggregationsAndFormatting =

IF(MIN('Measures YTD'[Formatting]) = "Number", FORMAT([TargetWithAggregations],"#,##0;(#,##0)"),

IF(MIN('Measures YTD'[Formatting]) = "Number with 1 Decimal", FORMAT([TargetWithAggregations],"#,##0.0;(#,##0.0)"),

IF(MIN('Measures YTD'[Formatting]) = "Percent", FORMAT([TargetWithAggregations], "0.0%"),

IF(MIN('Measures YTD'[Formatting]) = "USD Currency", FORMAT([TargetWithAggregations],"$#,##0;($#,##0)"),0))))

• Step 3: Use the following DAX Expression for the new ActualWithAggregationsAndFormatting calculated measure. Note, it is computed based on ActualWithAggregations measure created in the previous section.

ActualWithAggregationsAndFormatting =

IF(MIN('Measures YTD'[Formatting]) = "Number", FORMAT([ActualWithAggregations],"#,##0;(#,##0)"),

IF(MIN('Measures YTD'[Formatting]) = "Number with 1 Decimal", FORMAT([ActualWithAggregations],"#,##0.0;(#,##0.0)"),

IF(MIN('Measures YTD'[Formatting]) = "Percent", FORMAT([ActualWithAggregations], "0.0%"),

IF(MIN('Measures YTD'[Formatting]) = "USD Currency", FORMAT([ActualWithAggregations],"$#,##0;($#,##0)"),0))))

There are predefined Numeric Formats, for example, FORMAT([ActualWithAggregations], "Currency"). If using predefined Numeric formats, please keep in mind that the decimal places are preset.

• Step 4: Add the two new calculated Measures in the Matrix Values section. Note the difference for aggregations before removing the TargetWithAggregations and ActualWithAggregations from the Matrix Values section.

 To validate that the aggregation and formatting for the Targets and Actuals are as per the requirements set in the PerformanceYTD table, add the CalendarYearMonth field to the Matrix Rows, and the Aggregations and Formatting fields in the Matrix values. Then, click on the top left corner to select the  conjoined arrows to drill-down to the  CalendarYearMonth level.

Avg Orders per Customer KPI values for Targets and Actuals are averaged in the Total; Total Orders and Total Customers KPI values for Targets and Actuals are summed; and the rest show the latest CalendarYearMonth’s value. Likewise, the formatting for Targets and Actuals now matches the requirements.

Lastly, I am going to rename TargetWithAggregationsAndFormatting and ActualWithAggregationsAndFormatting calculated measures to Target and Actual in the Values section of the Power BI Desktop Matrix:

I hope you enjoyed this tutorial for custom aggregations and formatting for the Power BI Desktop Matrix. Stay tuned for the second blog installment, Power BI Desktop: Custom Aggregations, Formatting and Performance Indicators (Part 2), in which I will give step by step instructions on how to add color performance indicators next to the Actual value based on the associated Target value.

In part 2 of this post I will give a step by step tutorial on how to add color performance indicators next to the actual values.