[vc_row][vc_column width=”1/1″][vc_column_text el_class=”mb-adapt-3″]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.[/vc_column_text][vc_column_text el_class=”mb-adapt-3 mt-adapt-3″]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.[/vc_column_text][vc_column_text el_class=”mb-adapt-3 mt-adapt-3″]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
[/vc_column_text][vc_column_text el_class=”mb-adapt-3 mt-adapt-3″]Since the first two are covered in PowerBI Scorecards’ 101, we’ll focus on importing values automatically.[/vc_column_text][vc_custom_heading heading_semantic=”h3″ text_size=”h3″ el_class=”mb-adapt-3 mt-adapt-3″]Configuring automatic KPIs in PowerBI Scorecards[/vc_custom_heading][vc_column_text el_class=”mb-adapt-3 mt-adapt-3″]From the PowerBI Scorecards web agent, go to Settings and click on Connections.[/vc_column_text][vc_single_image media=”6973″ el_class=”mb-adapt-3 mt-adapt-3″][/vc_single_image][vc_column_text el_class=”mb-adapt-3 mt-adapt-3″]Click the Create button on the top right corner and a modal will open.[/vc_column_text][vc_single_image media=”6974″ el_class=”mb-adapt-3 mt-adapt-3″][/vc_single_image][vc_column_text el_class=”mb-adapt-3 mt-adapt-3″]We will now cover the configuration of SQL and DAX connections.[/vc_column_text][vc_custom_heading heading_semantic=”h4″ text_size=”h4″ el_class=”mb-adapt-3 mt-adapt-3″]Configuring automatic KPIs using a SQL connection[/vc_custom_heading][vc_column_text el_class=”mb-adapt-3 mt-adapt-3″]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.[/vc_column_text][vc_column_text el_class=”mb-adapt-3 mt-adapt-3″]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.
[/vc_column_text][vc_single_image media=”6977″ el_class=”mb-adapt-3 mt-adapt-3″][/vc_single_image][vc_custom_heading heading_semantic=”h4″ text_size=”h4″ el_class=”mb-adapt-3 mt-adapt-3″]Configuring automatic KPIs using a DAX connection[/vc_custom_heading][vc_column_text el_class=”mb-adapt-3 mt-adapt-3″]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).[/vc_column_text][vc_column_text el_class=”mb-adapt-3 mt-adapt-3″]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.[/vc_column_text][vc_single_image media=”6978″ el_class=”mb-adapt-3 mt-adapt-3″][/vc_single_image][vc_column_text el_class=”mb-adapt-3 mt-adapt-3″]Below is an example of a DAX template:[/vc_column_text][vc_column_text el_class=”mb-adapt-3 mt-adapt-3″]Please note that templates need to be set according to your own business logic.[/vc_column_text][vc_column_text el_class=”mb-adapt-3 mt-adapt-3″]DEFINE[/vc_column_text][vc_column_text el_class=”mb-adapt-3 mt-adapt-3″]The parameters below need to be on the template as well since they will be replaced with the formula defined in the KPI Configuration:[/vc_column_text][vc_column_text el_class=”mb-adapt-3 mt-adapt-3″]/*YearDateParam*/
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]
)
/*MonthDateParam*/
/*NumeratorFormulaParam*/
/*DenominatorFormulaParam*
/*NumeratorYTDFormulaParam*
/*DenominatorYTDFormulaParam*/[/vc_column_text][vc_column_text el_class=”mb-adapt-3 mt-adapt-3″]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 support@devscope.net.[/vc_column_text][vc_single_image media=”4420″ media_link=”url:https%3A%2F%2Fpowerbitiles.com%2F%3Fapp%3DPBIRobots%23utm_source%3DBlog%26utm_medium%3DBanner%26utm_campaign%3DBlog|target:_blank” el_class=”mb-adapt-3 mt-adapt-3″][/vc_single_image][/vc_column][/vc_row]