How to calculate Monthly Sales Target Surplus and Deficit values using DAX in Power BI

How to show Monthly Sales that Met the Target or Not Met using DAX in Power BI

Scenario:

Suppose we have the Data Model as follows.

The Relationships are as follows:

Based on the above Model we need to calculate and show which Months are Met the Target Sales or Not.

To fulfill the above Scenario, we need to create the below Measures:

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

I have defined a Dummy Target value per Month using the Average value of Sales across the Years per Month. You can define your Targets as per your data.

Target Val =

    AVERAGEX( ALL(tbl_Calendar[Year], tbl_Calendar[Month_Name]), [Net Sales])

Sales Base = MIN( [Net Sales], [Target Val])

Sales Cap = MAX( [Net Sales], [Target Val])

Now based on the above Measures, we will calculate the Monthly Sales which are above the Target (Surplus) or below Target (Deficit), as per below:

Target Deficit =

VAR vSales = [Sales Base]

VAR vTarget = [Target Val]

RETURN

IF(

    vTarget > vSales,

    [Sales Cap]-[Sales Base]

)

Target Surplus =

VAR vSales = [Sales Base]

VAR vTarget = [Target Val]

RETURN

IF(

    vSales >= vTarget,

    [Sales Cap]-[Sales Base]

)

The ratio of Target Deficit and Surplus values can calculated as per below:

% Target Deficit =

VAR vSales = [Sales Base]

VAR vTarget = [Target Val]

RETURN

IF(

    vTarget > vSales, DIVIDE(([Sales Cap]-[Sales Base]), [Sales Cap])

)

% Target Surplus =

VAR vSales = [Sales Base]

VAR vTarget = [Target Val]

RETURN

IF(

    vSales >= vTarget, DIVIDE(([Sales Cap]-[Sales Base]),[Sales Cap])

  )

Now we can calculate the no. of Months that Missed the Target using the blow logic :

MonthsMissedTarget =

VAR vSales = [Sales Base]

VAR vTarget =

    AVERAGEX( ALL(tbl_Calendar[Year],tbl_Calendar[Month_Name]), [Net Sales])

VAR vCountRows =

CALCULATE(DISTINCTCOUNT(tbl_Calendar[Month_Name]),

        FILTER(ALL(tbl_Calendar[Month_Name]), [Sales Base] < vTarget)

        )

RETURN

vCountRows

Next, I have defined a Title using the above Measure. This will be used further as a Sub Title for the Chart.

TargetTitle = "( " & [MonthsMissedTarget] &" Months Missed the Target )"

Finally, we can see the result of all the Measures in a Table and a Line and Stacked Column Chart as shown below:

Notes:

On the Line and Stacked Column Chart, I have used the Measures as per below.

Though, I have used the Target on the Line, I have made the Line Stroke Width to Zero, then enabled the Marker (-) with Blue Color.

--------------------------------------------------------------------------------------------------------

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

--------------------------------------------------------------------------------------------------------