ID45 - Automation goals (Power BI) Overview & Sample

Power BI report to show what areas of the business are automated

Jump to a specific section by clicking a link

Overview | Samples | Variables | Alert Functionality | Power BI Reporting | Related Alerts | Webinar

 

Click to Subscribe and Download Template

 

Overview

Overview

 

ID45 is a Power BI dashboard that will show you data points of your business that are automated or can be automated. For example what % of your contracts are entered manually versus entered by Juice. Same for ATRs, what % of your toner orders are entered manually versus automatically pushed in from a DCA. We have included the typical time these transactions take to enter and the typical hourly cost for that type of data entry to show you how much you could be saving. 

If you have been following Gary's blogs you will notice a theme of "Integrate & Automate". You can't truly scale a business unless you automate it and you can't automate if the various software you run are not integrated. The low-hanging fruit for automation are Deals, ATRs, and Accounts Receivable, yet only 10% of our customer base has ID815 in place. 

 

Type of Output: On Demand Report Power BI Report

 

*  *  *

Sample

Sample

 

*  *  *

Variables

Variables

NONE

Alert Functionality

Alert Functionality

-See here for getting Power BI in place. 

-Click here to download the file

For ECi Hosted clients: We are working with ECi to open up our access so we can set an ODBC connection for you. If you are interested, then please provide us the public static IP address for your company network so that ECi can whitelist it to allow us access.

 

 

*  *  *

Power BI Reporting

Power BI Reporting

Parameter & Configuration Tables

These are What-If parameter tables that expose sliders/selectors to report consumers.

#               Activity                                                                    Parameter Table

1               Create Sales Order Header                          Param: Create Sales Order Header

2               Enter Sales Order Line Item                         Param: Enter Sales Order Line Item

3               Update Sales Order On Hold Code          Param: Update Sales Order On Hold Code

4               Sales Order Status Update                           Param: Sales Order Status Update

5               Create Call (Service Call)                               Param: Create Call

6               Update Call (Service Call)                             Param: Update Call

7               Create Purchase Order                                    Param: Create Purchase Order

8               Create Contract Header                                 Param: Create Contract Header

9               Create Contract Details                                  Param: Create Contract Details

10            Create Meters                                                       Param: Create Meters

11            Equipment Updates                                        Param: Equipment Updates

12            Collection Tasks                                                 Param: Collection Tasks

13            Customer Email                                                  Param: Customer Email
14         Creators Considered Automated

 

Parameter KPIs

1. Param: Create Sales Order Header Value = Selected time in seconds to create a Sales Order Header. No default — slicer required. *** SELECTEDVALUE( Param: Create Sales Order Header[...] )
2. Param: Enter Sales Order Line Item Value = Selected time in seconds to enter a Sales Order Line Item. No default — slicer required. *** SELECTEDVALUE( Param: Enter Sales Order Line Item[...] )
3. Param: Update Sales Order OnHold Code Value = Selected time in seconds to update a Sales Order OnHold Code. No default — slicer required. *** SELECTEDVALUE( Param: Update Sales Order OnHold Code[...] )
4. Param: Sales Order Status Update Value = Selected time in seconds for a Sales Order Status Update. No default — slicer required. *** SELECTEDVALUE( Param: Sales Order Status Update[...] )
5. Param: Create Call Value = Selected time in seconds to create a Service Call. No default — slicer required. *** SELECTEDVALUE( Param: Create Call[...] )
6. Param: Update Call Value = Selected time in seconds to update a Service Call. No default — slicer required. *** SELECTEDVALUE( Param: Update Call[...] )
7. Param: Create Purchase Order Value = Selected time in seconds to create a Purchase Order. No default — slicer required. *** SELECTEDVALUE( Param: Create Purchase Order[...] )
8. Param: Create Contract Header Value = Selected time in seconds to create a Contract Header. No default — slicer required. *** SELECTEDVALUE( Param: Create Contract Header[...] )
9. Param: Create Contract Details Value = Selected time in seconds to create Contract Details. No default — slicer required. *** SELECTEDVALUE( Param: Create Contract Details[...] )
10. Param: Create Meters Value = Selected time in seconds to create Meters. No default — slicer required. *** SELECTEDVALUE( Param: Create Meters[...] )
11. Param: Equipment Updates Value = Selected time in seconds for Equipment Updates. No default — slicer required. *** SELECTEDVALUE( Param: Equipment Updates[...] )
12. Param: Collection Tasks Value = Selected time in seconds for Collection Tasks. Default = 5 seconds. *** SELECTEDVALUE( Param: Collection Tasks[...], 5 )
13. Param: Customer Email Value = Selected time in seconds for Customer Email. Default = 30 seconds. *** SELECTEDVALUE( Param: Customer Email[...], 30 )
14. Param: Creators Considered Automated = Users can now designate additional creators as automated directly from the report. A transaction is considered automated if it was performed by CeoJuice, Admin, or any creator selected from this list. The slicer has no effect when no selection is made.

Core KPIs *** Primary Business Measures

1. Transactions = Total ERP transactions (manual + automated). Base denominator for all automation rates. *** SUM( Activities[Record Count] )
2. Automated Transactions = Transactions executed by an automated process (CeoJuice/Admin creator). *** CALCULATE( [Transactions], Users[User Type] = "Automated" )
3. Manual Transactions = Transactions performed by human users. The remaining manual workload. *** CALCULATE( [Transactions], Users[User Type] <> "Automated" )
4. % Automation (Qty) = Share of transactions handled by automation. Only calculated when Saving Cost is not blank. *** IF( [Saving Cost] <> BLANK(), Automated Transactions ÷ Total Transactions )
5. % Automation ($) = Share of total potential labor cost eliminated by automation. *** IF( [Saving Cost] <> BLANK(), Saving Cost ÷ Total Labor Cost )
6. % Automation (Time) = Fraction of processing time handled by automation. Accounts for duration variation between activities. *** Automated Labor Hours ÷ Total Labor Hours
7. Entry Time (sec) = Total seconds to process all transactions, weighted by per-activity time parameters. *** SUMX( Activities, Transactions × SWITCH( Activity Order, 1→Create SO Header param, 2→Enter SO Line param, ... 13→Customer Email param ) )
8. Total Labor Hours = Total labor hours for all transactions (manual + automated). *** Entry Time (sec) ÷ 3,600
9. Automated Labor Hours = Hours of work taken off human plates by automation. Numerator for FTE calculations. *** SUMX( rows where Creator = "CeoJuice" or "Admin", Transactions × Time Param ) ÷ 3,600
10. Manual Labor Hours = Hours still spent by humans. The remaining manual workload target. *** SUMX( rows where Creator ≠ "CeoJuice" and ≠ "Admin", Transactions × Time Param ) ÷ 3,600
11. Total Labor Cost = Total dollar value of labor for all transactions. The "no automation" baseline cost. *** SUMX( Activities, Transactions × Labor Rate ($/sec) × Time Param )
12. Saving Cost = Dollar value of labor replaced by automation. The primary ROI figure. *** SUMX( rows where Creator = "CeoJuice" or "Admin", Transactions × Labor Rate ($/sec) × Time Param )
13. Manual Labor Cost = Actual remaining labor cost from human transactions. Saving Cost + Manual Labor Cost = Total Labor Cost. *** SUMX( rows where Creator ≠ "CeoJuice" and ≠ "Admin", Transactions × Labor Rate ($/sec) × Time Param )
14. Labor Rate (secs) = Per-second labor rate. Internal helper. At $45/hr → $0.0125/sec. *** Daily Admin Labor Rate ($/hr) ÷ 3,600
15. Workdays = Total effort as equivalent 8-hour workdays. *** Entry Time (sec) ÷ 3,600 ÷ 8
16. Manual Workdays = Workdays still borne by humans after subtracting automation. *** Total Workdays − ( Automated Labor Hours ÷ 8 )
17. FTE Capacity Released = Full-time equivalents freed by automation. 880 automated hours = 0.5 FTE released. *** Automated Labor Hours ÷ 1,760  (= 220 workdays × 8 hrs)
18. Capacity Released (Hours) = Alias for Automated Labor Hours with a capacity-focused label. *** = Automated Labor Hours
19. % Manual Workload = Inverse of % Automation (Qty). Together they sum to 100%. *** Manual Transactions ÷ Total Transactions
20. % Manual Effort (Time) = Proportion of processing time consumed by humans. Inverse of % Automation (Time). *** Manual Labor Hours ÷ Total Labor Hours
21. Manual Effort Index = Composite of time burden × volume. High values identify prime automation candidates. *** Manual Workdays × Manual Transactions
22. Automation Gap % = Percentage of transactions not yet automated. The remaining opportunity headroom. *** 1 − % Automation (Qty)
23. Automation Rate MoM Δ = Month-over-month change in automation rate. Positive = improving; Negative = regression. *** % Automation (Qty) [current month] − % Automation (Qty) [ DATEADD( Dates[Date], −1, MONTH ) ]
24. Automation Stability Flag = "Declining" when MoM automation rate has fallen. Used for alerts and conditional formatting. *** IF( Automation Rate MoM Δ < 0, "Declining", "Stable / Improving" )
25. Manual Workload Share = Share of the organization’s total workday burden in the selected activity/group. *** Manual Workdays [current context] ÷ Total Workdays [ ALL( Activities ) ]
26. Manual Effort Concentration = Share of all manual effort concentrated in the selected activity. *** Manual Workdays [current context] ÷ Total Manual Workdays [ ALL( Activities ) ]
27. Manual Capacity Pressure = Proportion of total processing time requiring human intervention. *** Manual Workdays ÷ Total Workdays

 

User Specific KPIs 

1. User Time Allocation % = Percentage of total manual hours this user accounts for in the current selection. *** Manual Labor Hours [user] ÷ Total Manual Labor Hours [ ALLSELECTED() ]
2. User Manual Work % = User's manual hours as share of the team's total hours including automated. *** Manual Labor Hours [user] ÷ Total Labor Hours [ ALL( Users ) ]
3. User Activity Participation Count = Number of distinct activity types the user has performed. *** DISTINCTCOUNT( Activities[Activity] )
4. Manual Labor Hours MoM Δ = Month-over-month change in manual hours. Positive = growing workload. *** Manual Labor Hours [current] − Manual Labor Hours [ DATEADD( −1 MONTH ) ]
5. User Activity Concentration = Highest time-allocation % across all activities. High = effort concentrated on one task. *** MAXX over all activities: User Time Allocation %
6. User Manual Hours Trend Index = Normalized index: >1 = above-average month; <1 = below-average. Spots workload spikes. *** Manual Labor Hours [current context] ÷ Manual Labor Hours [ ALLSELECTED( Dates ) ]

Opportunity KPIs 

1. Opportunity Exists = Binary gate: 1 if activity still has un-automated manual transactions. Required for all Opportunity scores. *** IF( Manual Transactions > 0 AND % Automation (Qty) < 100%, 1, 0 )
2. Opportunity Volume = Raw count of remaining manual transactions. Volume dimension of opportunity. *** = Manual Transactions
3. Opportunity Effort = Remaining manual workdays. Time/effort dimension of opportunity. *** = Manual Workdays
4. Opportunity Value = Dollar value of remaining manual work. Financial dimension of opportunity. *** = Manual Labor Cost
5. Opportunity Volume Score = Normalized volume score. Highest-volume eligible activity scores 1.0. *** IF( Opportunity Exists = 0, 0, Manual Transactions ÷ MAX( Manual Transactions across eligible activities ) )
6. Opportunity Effort Score = Normalized effort score. Weighted 40% in composite — the most important component. *** IF( Opportunity Exists = 0, 0, Manual Workdays ÷ MAX( Manual Workdays across eligible activities ) )
7. Opportunity Value Score = Normalized financial score. Uses Activity Type for max calculation (broader grouping). *** IF( Opportunity Exists = 0, 0, Manual Labor Cost ÷ MAX( Manual Labor Cost across eligible activity types ) )
8. Automation Opportunity Score = Headline 0–1 prioritization score. Rank activities by this to build your automation roadmap. *** ( Volume Score × 30% ) + ( Effort Score × 40% ) + ( Value Score × 30% )
9. Automation Eligible Activities = Total addressable universe of automation-eligible distinct activities. *** DISTINCTCOUNTNOBLANK( Activities[Activity] ) WHERE Automation Eligible = "Yes"

General Report Features

1. Access to Report Pages
Like all CeoJuice Power BI Reports, access to all report pages is given from the Landing / Index page.
Within the Landin / Index page you can also see when the report was last refresh, a revision log of all changes / additions of the current release and a link to site documentation.



2. Access to Report Pages
Within each report page you can access a filter / slicer panel from the top right corner.






3. Access to page documentation
Within each report page you can access current’s page documentation by selecting the ‘?’ icon located a the top right corner next to the filter icon.





Report Pages

Page 1 — Automation Executive Scorecard  ***Automation adoption, capacity released, and value realization (time & cost)

Scope

• Date slicer (Rolling Months)
• Slicers: Activities[Activity Group], Activities[Activity Type]
• Outputs: enterprise + drill to activity group

Expected outcome

Leadership can answer in <60 seconds:

• “How automated are we?”
• “How much labor/cost did we avoid?”
• “Where is the biggest remaining manual effort?”
• “Are we improving over time?”

Page 2 — Automation Coverage & Risk Exposure  *** Manual workload concentration & operational risk

Scope

• Manual vs automated workload
• High-volume, low-automation processes
• Risk and scalability analysis

 

Expected Outcome

• Leadership sees where manual work creates:
  • Cost risk
  • Scaling bottlenecks
  • SLA exposure
• Drives prioritization of next automation waves

Page 3 — Automation Operations Control  *** Monthly automation execution, workload balance and stability

Scope

• Monthly rollup data
• Operational view by:
  • Activity Group
  • Activity Type
• Manual vs automated execution
• Month-over-month changes
• Capacity and workload stress signals
• No real-time or SLA-minute assumptions

This report supports monthly operations reviews, not live incident management.

 

Expected Outcome

Operations leadership can:

• Confirm automation is stable and scaling
• Detect regressions or backsliding
• See where manual workload is increasing
• Distinguish:
  • Automation gaps (needs build)
  • Operational drift (needs fixing, training, or adoption)
• Decide what to fix next month

Page 4 — Automation Opportunity Pipeline ***Prioritized automation opportunities ranked by effort, volume and value

Scope

• Monthly rollup data
• Activity Group + Activity Type level
• Only automation-eligible work
• Manual workload only (automation already done is excluded)
• Effort, scale, and financial impact combined
• Designed for roadmap planning and investment decisions

 

Expected Outcome

Leadership and operations can:

• See a ranked list of automation candidates
• Quantify why something is a priority (not gut feel)
• Compare opportunities across very different processes
• Decide:
  • What to automate next
  • What to defer
  • What not to automate (low value)

Page 5 — User Time Allocation & Activity Participation  *** Distribution of user effort across and automation impact 

Scope

• Monthly rollup data
• User-level view of:
  • Activity participation
  • Time allocation (derived from thresholds)
  • Manual vs automated contribution
• Trends over time
• Focus on work distribution, not productivity scoring

This report answers:

 “How is operational effort distributed across users and activities, and how is automation changing that?”

 

Expected Outcome

Operations leadership and managers can:

• Understand where user time is actually spent
• Identify:
  • Overloaded users or roles
  • Highly repetitive manual assignments
• Validate that automation is:
  • Reducing low-value work
  • Shifting users toward higher-value activities
• Support:
  • Training decisions
  • Role redesign
  • Change management conversations

This report is capacity and allocation, not performance ranking.

 

Page 5 — Set your own automation goals   *** Leverage Power BI Service scorecards to set automation goals 

Scope

• Select any of the available KPIs from the list in order to monitor through Power BI service scorecards
  • Set your own goals and track performance against those goals

 

*  *  *

Related Alerts

Related Alerts

 

See full list of all Power BI reports that we currently have available

https://support.ceojuice.com/hc/en-us/sections/360011259132-Power-BI-Reports

 

*  *  * 

Webinar

Webinar