NCR Page
Summary
This page forms part of the Non-Conformance Report for Datacair. It consolidates data from both the NCR Database and the SupportPal Non-Conformance Tickets.
The report provides a unified view of non-conformance data across entities, combining records from both sources. It offers a high-level overview of staff attendance and work rates, enabling quick and easy analysis across all entities.
Filters
| Filter | Description |
|---|---|
| Date | Filters data by there creation dates |
| Entity | Filters data by there entities |
Data Transformation Applied
| Query | Transformations |
|---|---|
| Datacair tickects table API link =https://customer-service.datacair.com/api/ticket/ticket?department=4&limit=1000000000 |
|
| SharePoint Tables =https://dcbiaero.sharepoint.com/sites/NCRDatabase/ | Purpose: Connects to the NCR Database stored on SharePoint and retrieves all available tables and lists.
Details: Uses SharePoint API version 2.0 and loads all views in the site. Enables access to structured NCR (Non-Conformance Report) data for further transformation and reporting in Power BI. Usage: This source is used to supplement other datasets with NCR-related metadata or additional ticket details not available in the primary API. |
Calculations
| Columns | Calculation | Description |
|---|---|---|
| 1 - Total Transactions | Total Transactions =
COALESCE([Number of Bookings], 0) + COALESCE([Number of Email], 0) + COALESCE([Number of FDC], 0) + COALESCE([Number of Pre_Quotes], 0) |
This represents the total number of all transaction types completed. It is calculated by adding together the number of bookings, emails, FDC , and pre-quotes. If any of these values is missing, it is treated as zero to ensure accurate counting. |
| 2 - Total Transactions Affected | Total Transactions Affected = SUM('NCR_page_datacaire_tickets'[39_Trasanction_Affected]) +0 | This represents the total number of transactions impacted, calculated by summing the values in the '39_Transaction_Affected' column from the 'NCR_page_datacaire_tickets' dataset. A "+0" ensures that the result is always a number, even if the dataset is empty or no transactions are affected. |
| 3 - Total NCR's | Total NCR's =
COALESCE(DISTINCTCOUNT('NCR_page_datacaire_tickets'[id]), 0) |
This represents the total number of unique Non-Conformance Reports (NCRs). It is calculated by counting the distinct values in the 'id' column of the 'NCR_page_datacaire_tickets' dataset. If no unique IDs are found, the result is set to zero to ensure an accurate count. |
| 4 - Staffed Rostered | Staffed Rostered = COALESCE(SUM('NCR Database Combined'[Staff Rostered]), 0) | This represents the total number of staff scheduled to work, calculated by summing the values in the 'Staff Rostered' column of the 'NCR Database Combined' dataset. If no data is available or the sum is empty, the result is set to zero for accuracy. |
| 5 - Staff Attended | Staff Attended = COALESCE(SUM('NCR Database Combined'[Staff Attended]), 0) | This represents the total number of staff who actually attended work, calculated by summing the values in the 'Staff Attended' column of the 'NCR Database Combined' dataset. If no data is available or the sum is empty, the result is set to zero to ensure an accurate count. |
| 6 - Accuracy | Accuracy = IFERROR(
1 - DIVIDE( CALCULATE([Total Transactions Affected]), CALCULATE([Total Transactions]) ), 0 ) |
This measures the accuracy of transactions by calculating the proportion of transactions that were not affected. It is determined by dividing the Total Transactions Affected by the Total Transactions and subtracting the result from 1. If there’s an error (e.g., division by zero), the result is set to zero to ensure a valid output. |
| 7 - Attendance % | Attendance % =
DIVIDE( [Staff Attended], [Staffed Rostered], 0 ) |
This represents the percentage of rostered staff who actually attended work. It is calculated by dividing the total number of Staff Attended by the total number of Staffed Rostered. If the calculation cannot be performed (e.g., if Staffed Rostered is zero), the result is set to zero to ensure a valid output |
| 8 - Accuracy by Date | Accuracy = IFERROR(
1 - DIVIDE( CALCULATE([Total Transactions Affected]), CALCULATE([Total Transactions]) ), 0 ) |
This chart visually displays the Accuracy percentage over time, with each point on the line representing the Accuracy for a specific date. Accuracy is calculated as 1 minus the ratio of Total Transactions Affected to Total Transactions, expressed as a percentage. If the calculation encounters an error (e.g., no transactions), it defaults to 0%. The x-axis shows the dates, while the y-axis shows the Accuracy percentage (0% to 100%). This chart helps users track how transaction accuracy trends over time, identifying patterns or fluctuations in performance. |
| 9 - Staff by Date | Staffed Rostered = COALESCE(SUM('NCR Database Combined'[Staff Rostered]), 0)
Staff Sickness = COALESCE(SUM('NCR Database Combined'[Staff Sickness]), 0) Staff on Leave = COALESCE(SUM('NCR Database Combined'[Staff on Leave]), 0) Staff Attended = COALESCE(SUM('NCR Database Combined'[Staff Attended]), 0)" |
This line chart visualizes four key staff metrics over time: Staffed Rostered, Staff Sickness, Staff on Leave, and Staff Attended.
|
| 10 - Total Transactions by Month | Total Transactions =
COALESCE([Number of Bookings], 0) + COALESCE([Number of Email], 0) + COALESCE([Number of FDC], 0) + COALESCE([Number of Pre_Quotes], 0) |
This column chart displays the Total Transactions for each month, providing a clear view of transaction volume over time. Total Transactions is calculated by summing the number of bookings, emails, FDC (Final Delivery Confirmations), and pre-quotes, with each component defaulting to 0 if no data is available. The x-axis lists the months, while the y-axis shows the total count of transactions. Each column represents the combined transaction activity for a given month, helping users identify trends, peak periods, or fluctuations in transaction volume. |
| 11 - | i. Date
ii. Staffed Rostered iii. Staff Attended iv. Staff on Leave v. Staff Sickness vi. Number of Bookings vii. Number of Emails viii. Number of Prequotes ix. Number of FDC x. Total NCRs xi.Total Transactions xii. Total Transactions Affected xiii. Accuracy |
The table presents a comprehensive overview of key metrics related to transactions and staffing, visualized in a clear, tabular format for easy analysis |