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

by Dianapo Visitor on ‎02-20-2018 04:46 PM - last edited on ‎04-03-2018 10:16 AM by Administrator MindyJ

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 

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

 

initialmatrix.JPG

 

 

subtotals.JPG

 

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:

 

 

New Measure.JPG

 

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

 

CustomAggregations.JPG

 

 

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.

 

 

CustomAggregationsAndFormatting.JPG

 

 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.

 

DrillDown.JPGAvg 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:

 

 

 

rename.JPG

 

             

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.

Comments
by sujit Frequent Visitor
on ‎02-21-2018 04:48 AM

For the below code I am unable to apply table function through Json file. Please find below code. 

 

{"name":"My Theme",
"background":"#212121",
"foreground":"#000000",
"tableAccent":"#E2A84E",
"dataColors":[
"#F9A825",
"#03A9F4",
"#C0CA33",
"#FFCA28"
],

"visualStyles":
{"*":
{"*":
{"*":[{"fontSize":10,"fontFamily":"Calibri, Cambria","color":{"solid":{"color":"#4fc3f7"}}}]


}
},

"table":
{"*":
{ "columnHeaders":[{"outline":"TopOnly","fontColor":{"solid":{"color":"#ffffff"}},
"backColor":{"solid":{"color":"#009688"}},"fontFamily":"Calibri, Cambria","fontSize": 12,
"autoSizeColumnWidth": true,"urlIcon":true}]

 

}
}


}
}

 

 

Table is under the shade of black, probably taking from mytheme declared values, not from table card. Your help will be appreciated. 

by Dianapo Visitor
on ‎04-03-2018 02:09 PM

Sujit, I believe you meant to post your question for the Custom Theme blog (this is about matrix aggregations). 

by ScottKaysee Frequent Visitor
on ‎04-05-2018 04:57 PM

Gday Diana... thanks for the blog post, working through it now. Just a quick note to say there is a naming mismatch between your supplied table and formual code samples... "PerformanceYTD" v "Measures YTD". No biggie though.

 

Cheers

by Dianapo Visitor
on ‎04-05-2018 05:24 PM

ScottKaysee, good point - I did name the dataset locally in Power BI as MeasureYTD but didn't coordinate it with the Excel file name, thinking it is not showing in the snapshots but of course it does in DAX. Thanks for checking it out and would like to take the opportunity to let you know that part 2 just got published and it goes over adding color indicators in standard matrix (first time available onlineSmiley Happy