Scenario:
Suppose we have the Data Model 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)
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())
We can build a Progress bar for Actuals vs Target, using the 100% Stacked Bar Chart as shown below:
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]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
--------------------------------------------------------------------------------------------------------
No comments:
Post a Comment
Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.