What is KPI and how to create in Power BI using Tabular Editor | Power BI Blog

What is KPI and how to define in Power BI using Tabular Editor
The Key performance indicator (KPI) refers to a quantifiable measurement which will be used to gauge a Company’s overall long-term performance. KPIs are specifically helps determine the Company’s strategic, financial, and operational achievements.

Eg: Net Profit or Profit Margin.

In Power BI, starting from the July 2020 version, we can define and create a KPI directly in the Power BI Desktop using external tools to modify its internal Tabular model, so that it can be used in any Power BI report and also by using the Analyze in Excel feature.

The KPI feature was previously available only in Tabular models created in Analysis Services or Power BI Premium.

Scenario:

Now lets go through, how to define KPI based on Profit Margin in Power BI using the Tabular Editor.

Power BI Model:

In the following Scenario, we will define KPI to analyze Growth/Trend of the Profit Margin % on a YoY Basis.

1) Create the base Measures needed for the KPI

We primarily define the following measures in Power BI which will be further using to define the KPI in Tabular Editor

Total Sales = SUMX ( tbl_Sales, ( tbl_Sales[Unit_Price] * tbl_Sales[Units_Sold] ))

Total Cost = SUMX ( tbl_Sales,( tbl_Sales[Units_Sold] * tbl_Sales[Unit_Cost] ))
Profit Margin = [Total Sales]-[Total Cost]
Profit Margin% = DIVIDE([Profit Margin],[Total Sales],0)
PY MarginPercent = 
    CALCULATE (
        [Profit Margin%],
        SAMEPERIODLASTYEAR( ‘tbl_Calendar'[cDate] )
    )
Target Margin% = 
VAR vTarget = ROUND ( CALCULATE( [Profit Margin%], REMOVEFILTERS () ), 2 )
RETURN
IF([Total Sales]=BLANK(),BLANK(), vTarget)

2) Define the KPI Measures based on Profit Margin% using Tabular Editor.

First Connect our Power BI Model to the Tabular Editor

Next right-click on the Profit Margin% measure > Create New > KPI

Now the Measure will be Converted to KPI, for which we need to define the Target, Status and Trend expressions as follows:

Target Expression:

Use the measure [Target Margin%] as the Target Expression for the KPI

Margin% Status Expression:

Use the below logic for the Margin% Status Expression.

-- Margin Status Expression
VAR vMarginPercent = [Profit Margin%]
VAR vMarginTolerance = 0.035
VAR vMarginGoal = [Target Margin%] --[_Profit Margin% Goal]
RETURN
    IF (
        NOT ISBLANK ( vMarginPercent ),
        SWITCH (
            TRUE,
            vMarginPercent < vMarginGoal - vMarginTolerance, -1,  -- Negative
            vMarginPercent > vMarginGoal + vMarginTolerance, 1,   -- Positive
            0
        )
    )

The Margin% Status represents , whether the Margin% is Greater or Lesser than Margin Goal or Target.
We can choose the Status Indicator, say “Traffic Light” which displays the Green Light when the Margin% is Greater than Target, and Red Light when Margin% is Lesser than Target.

Margin% Trend Expression:

Use the below logic for the Margin% Trend Expression.

-- Margin% Trend Expression
VAR vMarginPercent = [Profit Margin%]
VAR vPrevMarginPercent =
    CALCULATE (
        [Profit Margin%],
        PREVIOUSYEAR( 'tbl_Calendar'[cDate] )
    )
RETURN
    IF (
        NOT ISBLANK ( vMarginPercent ) && NOT ISBLANK ( vPrevMarginPercent ),
        SWITCH (
            TRUE,
            vMarginPercent > vPrevMarginPercent, 1,  -- Positive
            vMarginPercent < vPrevMarginPercent, -1, -- Negative
            0
        )
    )

The Margin% Trend represents , whether the Margin% is going in Upward or Downward when compare with the same period last Year.
We can choose the Trend Graphic Indicator, say “Standard Arrow” which shows Up Trend or a Down Trend based on the Margin% vs. PY Margin%.