How to configure automatic KPIs in PowerBI Scorecards

Joel Monteiro
March 15, 2021

PowerBI Scorecards is a powerful web tool that uses Power BI to display an organization’s scorecards. These scorecards are made up of several KPIs, both individual and collective, that, together, display an organization’s overall health. As the name implies, Microsoft’s Power BI is the tool used to visualize the scorecard because its versatility makes it easy to gauge the organization’s performance but also to detect the areas that need improvement.

This all sounds great, but there’s a big barrier when it comes to KPIs: workers really dislike tracking them, and the biggest culprit is having to manually enter the KPIs values. Because we experienced this first hand, our team made KPI automation a cornerstone feature of PowerBI Scorecards.

You can import data in PowerBI Scorecards using 3 methods:

    • Manual KPI input
    • Importing KPI values through an Excel file
    • Importing KPI values automatically:
      • using a SQL Database connection
      • using a Microsoft Analysis Service connection

Since the first two are covered in PowerBI Scorecards’ 101, we’ll focus on importing values automatically.

Configuring automatic KPIs in PowerBI Scorecards

From the PowerBI Scorecards web agent, go to Settings and click on Connections.

How to configure automatic KPIs in PowerBI Scorecards

Click the Create button on the top right corner and a modal will open.

How to configure automatic KPIs in PowerBI Scorecards - SQL or DAX

We will now cover the configuration of SQL and DAX connections.

Configuring automatic KPIs using a SQL connection

To configure a SQL Connection, you must name it and choose SQL as its type. Then, under Value, you must enter the connection string where the values will be fetched. When you’re done, press create.

Using the SQL Connection is pretty straightforward as you only need to choose this type of connection in the configuration of the KPI and input the formula in the fields as in the example shown below:

    • In the green box: set the KPI to automatic.
    • In the red box: choose the type of SQL connection string.
    • In the blue box: input the formula or query that to be executed to get this KPI’s value.

How to configure automatic KPIs in PowerBI Scorecards - configuring SQL

Configuring automatic KPIs using a DAX connection

The automatic KPI configuration using a DAX Connection is exactly the same, but you’ll need a template that returns the values exactly by this order: Area/SubArea/KPIValueNumerator/KPIDenomitanator(if applicable)/KPINumeratorYTD(if applicable)/KPIDenominatorYTD(if applicable).

Once again, click Settings from the menu and choose Connections. Name your connection and choose DAX as its type. Paste your connection string under Value and the template that returns your values under Template.

How to configure automatic KPIs in PowerBI Scorecards - configuring DAX

Below is an example of a DAX template:

Please note that templates need to be set according to your own business logic.

DEFINE
VAR yearFilter =
FILTER ( KEEPFILTERS ( VALUES ( Calendar[Year] ) ), Calendar[Year] = /*YearDateParam*/ )
VAR monthFilter =
FILTER ( KEEPFILTERS ( VALUES ( Calendar[Month (#)] ) ), Calendar[Month (#)] = /*MonthDateParam*/ )
evaluate
SELECTCOLUMNS (
SUMMARIZECOLUMNS (
'Calendar'[Month (#)],
'Calendar'[Year],
'Stock Item'[Category],
yearFilter,
monthFilter,
"Numerator", /*NumeratorFormulaParam*/,
"Denominator", /*DenominatorFormulaParam*/,
"NumeratorYTD", /*NumeratorYTDFormulaParam*/,
"DenominatorYTD", /*DenominatorYTDFormulaParam*/
),
"Data", DATE ( [Year], [Month (#)], 1 ),
"Area", [Category],
"SubArea", "ALL",
"Numerator", [Numerator],
"Denominator", [Denominator],
"NumeratorYTD", [NumeratorYTD],
"DenominatorYTD", [DenominatorYTD]
)

The parameters below need to be on the template as well since they will be replaced with the formula defined in the KPI Configuration:

/*YearDateParam*/
/*MonthDateParam*/
/*NumeratorFormulaParam*/
/*DenominatorFormulaParam*
/*NumeratorYTDFormulaParam*
/*DenominatorYTDFormulaParam*/

Thank you for making it this far. We hope this guide was comprehensive and compelling enough to make your organization configure its own automatic KPIs. If you still need help doing so, our team is always happy to help at [email protected].

PowerBI Robots banner
Next article

Estimating the wind speed of storms with DevScope

The DevScope AI & ML team joined a global Data Science for good challenge to estimate the wind speed of storms. This is their story.

Joel Monteiro
March 3, 2021