Skip to main content

Facility Formulas

Facility Management Fields with Formulas and Data Sources

Primary KPIs (Dashboard Level)

1. Total Facilities

  • Formula: COUNT(DISTINCT Facility_ID)
  • Data Source: Facilities Registry table
  • Description: Count of all registered facilities regardless of status

2. Operational Facilities

  • Formula: COUNT(Facilities) WHERE Status = 'Operational'
  • Data Source: Facilities Registry table (Status field)
  • Description: Count of facilities with operational status

3. Average Condition Score

  • Formula: SUM(Facility_Condition_Score) / SUM(Facility) (for operational facilities only)
  • Data Source:
    • Facilities Registry table (Condition_Score field)
    • Asset Registry table (aggregated asset condition scores)
  • Description: Weighted average condition score across operational facilities

4. Total Capacity

  • Formula: SUM(Design_Capacity) for operational facilities
  • Data Source: Facilities Registry table (Design_Capacity field)
  • Description: Combined design capacity in MLD of operational facilities

Facility Registry Table Fields

5. Systems Count

  • Formula: COUNT(Systems) WHERE Parent_Facility_ID = [This Facility_ID]
  • Data Source: Systems Registry table
  • Description: Number of child systems associated with facility

6. Assets Count

  • Formula: COUNT(Assets) WHERE Parent_Facility_ID = [This Facility_ID]
  • Data Source: Asset Registry table
  • Description: Total individual assets registered to systems that are linked to the facility

7. Facility Condition Score

  • Formula: Average of all asset condition scores within the facility
  • Data Source: Asset Registry table (Condition_Score field for all facility assets)
  • Description: Aggregated health score from underlying assets

8. Current Capacity (vs Design)

  • Formula: Calculated based on Service Order Data
  • Data Source:
    • From the service orders that are created for this facility with the Operation Type Flow Capacity
    • Facilities Registry table (Design_Capacity field)
  • Description: operational capacity vs design capacity

Facility Detail View - Key Metrics

9. Current Capacity Percentage

  • Formula: (Current Capacity / Design Capacity) × 100
  • Data Source:
    • From the service orders that are created for this facility with the Operation Type Flow Capacity
    • Facilities Registry table (Design_Capacity field)
  • Description: Percentage utilization with progress bar visualization

10. Active Systems Count

  • Formula: COUNT(Systems) WHERE Parent_Facility_ID = 'FAC-001' AND Status = 'Active'
  • Data Source: Systems Registry table
  • Description: Number of active operational systems

11. Connected Networks Count

  • Formula: COUNT(Networks) WHERE Connected_Facility_ID = 'FAC-001'
  • Data Source: Networks Registry table
  • Description: Number of distribution/collection networks connected

12. Facility Age

  • Formula: Current Date - Commissioned Date (in years)
  • Data Source: Facilities Registry table (Commissioned_Date field)
  • Description: Operational age since commissioning

Performance Tab Metrics

13. Facility Condition Index

  • Formula: Average of condition scores of all assets in facility
  • Data Source: Asset Registry table
  • Description: Composite facility health score

14. Asset Availability

  • Formula: (SUM(Scheduled Operating Time) - SUM(Unplanned Downtime)) / SUM(Scheduled Operating Time) × 100
  • Data Source:
    • Scheduled Operating Time is calculated from the operational hours multiplied by days, and downtime is fetched from service orders created for those assets
    • Maintenance records
  • Description: Percentage of time assets were operational

15. Out of Service Assets

  • Formula Count: COUNT(Assets) WHERE Parent_Facility_ID = [This Facility] AND Status = 'Out of Service, undermaintenance'
  • Formula Percentage: (Out of Service Count, under maintenance / Total Assets) × 100
  • Data Source: Asset Registry table (Status field)
  • Description: Count and percentage of inactive assets

16. Average Remaining Life

  • Formula: SUM(Asset RUL) / COUNT(Assets) for all facility assets
  • Data Source:
    • Asset Registry table (Remaining_Useful_Life field), can be calculated from the useful life - age
    • Asset condition assessments
  • Description: Average years remaining before replacement

17. Inspection Compliance

  • Formula: (COUNT(Completed Inspections) / COUNT(Scheduled Inspections)) × 100
  • Data Source:
    • Service Orders table (inspection type, completion status)
    • Maintenance schedules
  • Description: Percentage of scheduled inspections completed on time

18. Overall Risk Score

  • Formula: average of risk scores of all assets
  • Data Source:
    • Asset Registry table (Risk_Score)
    • Risk assessment records
  • Description: Aggregated facility risk combining probability and consequence

19. Service Level Achievement

  • Formula: (Times Met Service Level / Total Times) × 100
  • Data Source:
    • When service orders with the type of water quality are completed and approved calculate total number of times that type service orders is completed which becomes your total times and in those total times calculate the number of time all the readings of the service order are in range that will be your times met service level
  • Description: Percentage of time service targets were met

Energy Consumption Metrics

20. Energy Efficiency

  • Formula: Total kWh Consumed ÷ Total Megaliters (ML) Produced
  • Data Source:
    • Total Kwh Consumed can be Picked from the bill uploaded, total Megaliters produced is sum of capacities of all the flow capacity service orders
  • Description: Energy used per unit of output (kWh/ML)

21. Monthly Energy Cost

  • Formula: Directly fetched from the bill uploaded
  • Data Source:
    • Energy monitoring systems
    • Utility billing data
  • Description: Total energy cost for current period

Finance Tab Calculations

22. Current Book Value

  • Formula: sum of current value of all the assets in the facility
  • Data Source:
    • Asset current value
  • Description: Net accounting value

23. Annual Depreciation

  • Formula: sum of(Initial Capital Cost - Salvage Value) / Design Useful Life (Years) for all assets
  • Data Source:
    • Initial_Capital_Cost from each asset, Design_Useful_Life from each asset, Depreciation_Schedule from facility add from
    • Salvage_Value (currently 0)
  • Description: Yearly depreciation expense

24. Depreciation To Date (Current Year)

  • Formula: Annual Depreciation × (Months Passed in Fiscal Year / 12)
  • Data Source:
    • Calculated annual depreciation
    • Current fiscal year calendar
  • Description: Year-to-date depreciation recognized

25. Remaining Depreciation

  • Formula: Current Book Value - Salvage Value
  • Data Source:
    • Calculated current book value
  • Description: Future depreciation to be charged

Compliance Tab Metrics

26. Samples Taken (Current Month)

  • Formula: COUNT(Water Quality Service Orders) WHERE Sample_Date IN current month
  • Data Source: Service Orders table (Water Quality type, Sample_Date)
  • Description: Monthly regulatory samples collected

27. Parameters Monitored

  • Formula: COUNT(DISTINCT Parameter) for facility
  • Data Source: Water Quality Service Orders table (Parameter field)
  • Description: Distinct water quality parameters monitored

28. Exceedances (Last 30 Days)

  • Formula: COUNT(Samples) WHERE Result outside regulatory limit AND Sample_Date within last 30 days
  • Data Source:
    • Water Quality Service Orders table (Result, Sample_Date)
    • Regulatory standards table (limits)
  • Description: Recent regulatory limit violations

Alerts Tab Calculations

29. Emergency Work Orders Count

  • Formula: COUNT(WOs) WHERE Facility_ID = [This Facility] AND Priority IN ('Emergency', 'Critical') AND Status = 'Open'
  • Data Source: Service Orders table (Priority, Status, Facility_ID)
  • Description: Open high-priority work orders

30. SLA Breaches Count

  • Formula: COUNT(SLA_Breach_Service Orders) WHERE Facility_ID = [This Facility]
  • Data Source: Service Orders table (SLA breach indicators)
  • Description: Service level agreement violations

31. Overdue Tasks Count

  • Formula: COUNT(WOs) WHERE Facility_ID = [This Facility] AND Due_Date < Current_Date
  • Data Source: Service Orders table (Due_Date, assignment status)
  • Description: Work orders past due date

32. Work Order Backlog

  • Formula Count: COUNT(WOs) WHERE Facility_ID = [This Facility] AND Status IN ('Open', 'Scheduled')
  • Formula Hours: SUM(Estimated_Duration) for backlog work orders
  • Data Source: Service Orders table (Status, Estimated_Duration)
  • Description: Pending work orders and estimated hours

Predictive Maintenance Priority Score

33. Maintenance Priority Score (MPS)

  • Formula: Risk Score × (1 - (Performance Index / 100))
  • Data Source:
    • Asset Registry table (Risk_Score, Performance_Index)
    • Asset condition assessments
  • Description: Prioritization score for maintenance scheduling

Systems and Networks Counts

34. System Assets Count

  • Formula: COUNT(Assets) WHERE Parent_System_ID = [This System_ID]
  • Data Source: Asset Registry table
  • Description: Assets belonging to specific system

35. Network Assets Count

  • Formula: COUNT(Assets) WHERE Parent_Network_ID = [This Network_ID]
  • Data Source: Asset Registry table
  • Description: Assets (pipes, valves, hydrants) in network
No Comments
Back to top