Before and after automation

 To create a powerful visualization in Power BI that showcases the differences in task closing times and Full-Time Equivalent (FTE) reductions before and after automation, you can follow these steps. You’ll need to focus on comparing key performance metrics, such as task closing rates and FTE usage, before automation began and after automation was implemented.

Here’s a high-level plan of action:

Steps to Create a Visualization for Before and After Automation

1. Data Preparation

Step 1: Add a Column to Differentiate Between "Before" and "After" Automation

You need to categorize the data into two groups: before automation and after automation.

• If you have a column that contains the date when automation started for each task (e.g., RTSK Automation Start Date), you can use a calculated column in Power BI to create this categorization:

  DAX
  Automation_Period = IF('Table'[RTSK Closed At] < 'Table'[RTSK Automation Start Date], "Before Automation", "After Automation")
  

Step 2: Aggregate FTE and Task Metrics

You’ll need to aggregate FTE and task metrics by period. These metrics could include:

• Task Closure Rate (before vs. after automation)
• FTE Utilization (before vs. after automation)
• Task Completion Time (before vs. after automation)

Step 3: Create New Measures for Aggregations

You can create measures in Power BI to calculate the relevant metrics for both periods.

• Measure for FTE Reduction:

  DAX
  Total_FTE_Before = CALCULATE(SUM('Table'[FTE per month]), 'Table'[Automation_Period] = "Before Automation")
  Total_FTE_After = CALCULATE(SUM('Table'[FTE per month]), 'Table'[Automation_Period] = "After Automation")
  
  FTE_Reduction = Total_FTE_Before - Total_FTE_After
  

• Measure for Task Completion:

  DAX
  Total_Tasks_Before = CALCULATE(COUNT('Table'[RTSK Number]), 'Table'[Automation_Period] = "Before Automation")
  Total_Tasks_After = CALCULATE(COUNT('Table'[RTSK Number]), 'Table'[Automation_Period] = "After Automation")
  

• Measure for Task Closing Time (Average):

  DAX
  Avg_Task_Close_Time_Before = CALCULATE(AVERAGE('Table'[Task Age]), 'Table'[Automation_Period] = "Before Automation")
  Avg_Task_Close_Time_After = CALCULATE(AVERAGE('Table'[Task Age]), 'Table'[Automation_Period] = "After Automation")
  

2. Create Visualizations in Power BI

1. Bar Chart: Tasks Completed Before and After Automation

• X-Axis: Automation Period ("Before Automation", "After Automation").
• Y-Axis: Number of tasks completed (use the Total_Tasks_Before and Total_Tasks_After measures).
• This chart will help to visualize whether there has been an increase or decrease in task completion after automation was implemented.

2. Bar Chart or Line Chart: FTE Utilization Before and After Automation

• X-Axis: Automation Period.
• Y-Axis: FTE per month (use the Total_FTE_Before and Total_FTE_After measures).
• You can show the FTE Reduction as a line or bar chart, indicating how automation impacted resource utilization.

3. Line Chart: Task Closure Time Before and After Automation

• X-Axis: Date (RTSK Closed At).
• Y-Axis: Average Task Age (use the Avg_Task_Close_Time_Before and Avg_Task_Close_Time_After measures).
• You can visualize how task closure times have changed over time with automation, by plotting the trend lines before and after the automation date.

4. Pie Chart: Distribution of Tasks Completed Manually vs. Automated

• You can also show the split between manual and automated tasks by creating a calculated column for automation status (manual vs. automated) and using it in a pie chart to show the proportion of tasks completed manually vs. with automation.

  DAX
  Automation_Type = IF('Table'[RTSK Automated Task] = TRUE(), "Automated", "Manual")
  

  Use this column in a Pie Chart to show the percentage of tasks handled by automation.

3. Create a Comparative Dashboard

• Step 1: Add all the charts (Bar, Line, Pie) to a single Power BI Dashboard.
• Step 2: Use slicers for filtering by Region, Task Type, Date Range, etc., to give more flexibility in exploring the data.
• Step 3: Include KPIs for FTE savings, task completion rate, and average task closure time before and after automation.

4. Conclusion: Insights to Show

• Task Completion Trends: Demonstrate if task completion improved after automation.
• FTE Utilization: Highlight the reduction in resources needed post-automation.
• Efficiency Improvement: Use task closure times to showcase how automation has accelerated processes.
• Manual vs. Automated Distribution: Illustrate the percentage of tasks completed manually vs. with automation and the impact on overall efficiency.

DAX Example for Custom Measures:

• Task Completion Rate:

  DAX
  Task_Completion_Rate_Before = DIVIDE(Total_Tasks_Before, Total_Tasks_Before + Total_Tasks_After)
  Task_Completion_Rate_After = DIVIDE(Total_Tasks_After, Total_Tasks_Before + Total_Tasks_After)
  

• FTE Saved by Automation:

  DAX
  FTE_Saved = Total_FTE_Before - Total_FTE_After
  

Final Suggestions:

• Make use of Tooltips in Power BI to provide additional context when hovering over the charts.
• Add text boxes to your dashboard that explain the findings, such as the percentage reduction in FTE or the improvement in task completion rate.

This approach will help you communicate the impact of automation effectively, both in terms of task closure rates and resource savings. Let me know if you need help with specific DAX formulas or Power BI settings!