How to show Monthly Actual Sales vs Target on a Progress Bar in Power BI

How to make a Progress Bar to show Monthly Actual Sales vs Target in Power BI

Scenario:

Suppose we have the Data Model as follows.

The Relationships are as follows:

From above Model, we need to calculate and show the Progress of the Monthly Actual Sales vs Sales Target. 

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

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

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

Target Sales =

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

% Actuals vs Target =

VAR vActuals = [Net Sales]

VAR vTarget = [Target Sales]

RETURN

DIVIDE( (vActuals-vTarget),vTarget)

For the Target Achieved value I am considering only 100% as Cap, even if the Actuals Sales are more than the Target:

% Target Achieved =

MIN(1 + [% Actuals vs Target],1)

% Target Remain =

MAX(0-[% Actuals vs Target],0)

The below Measures will be used as Labels to show the Target achievement Rate on Cards:

Act vs Target Rate = ABS([% Actuals vs Target])

Target Label =

SWITCH( TRUE(),

    [% Actuals vs Target]>0, "Above Target",

    [% Actuals vs Target]<0, "Below Target",

    BLANK())

Target Indicator =

SWITCH( TRUE(),

    [% Actuals vs Target]>0, UNICHAR(128994),

    [% Actuals vs Target]<0,  UNICHAR(128308),

    BLANK())

Now we can view the result of the above Measures in a Table visual as follows:

Method 1 : Actuals vs Forecast Progress Bar with 100% Stacked Bar Chart :

We can build a Progress bar for Actuals vs Target, using the 100% Stacked Bar Chart as shown below:

Notes: 

For the Red and Green Indicator, I have used the measure [Target Indicator] defined above.

For the Target Surplus or Deficit ( above or below target) value, I have used the Cards with the measures [Act vs Target Rate] and [Target Label]

The Dimensions/Measures for Y-Axis and X-Axis are chosen as per below:

Method 2 : Actuals vs Forecast Progress Bar with Donut Chart :

We can build a Progress bar for Actuals vs Target, using the Donut Chart as shown below.

Notes: 

For the Red and Green Indicator, I have used the measure [Target Indicator] defined above.

For the Target Surplus or Deficit ( above or below target) value, I have used the Cards with the measures [Act vs Target Rate] and [Target Label]

The Values for Donut are chosen as per below:

Additional Notes:

Color Codes for reference to create the Gradient Effects for a Field based on its Value.

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

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

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