NCR Report Analysis

Filters

Filter	Description

Data Transformation Applied

Query	Transformations
Datacair tickects table API link =https://customer-service.datacair.com/api/ticket/ticket?department=4&limit=1000000000	API Endpoint: https://customer-service.datacair.com/api/ticket/ticket?department=4&limit=1000000000 Purpose: Retrieves comprehensive ticket information for department ID 4, including metadata and key lifecycle fields. The limit parameter is explicitly set to a very high value to ensure all relevant records are returned in a single call, bypassing default pagination limits.
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 NCRs Raised By Entity, Region and Dates	Total NCR's = COALESCE(DISTINCTCOUNT('NCR_page_datacaire_tickets'[id]), 0)	The Total NCRs metric shows the number of unique Non-Conformance Reports (NCRs) by counting distinct ticket IDs from the 'NCR_page_datacaire_tickets' dataset. If no unique IDs are found, it defaults to 0. The table displays Total NCRs alongside dates, entities, and regions. Each row shows the count of unique NCRs for a specific date, entity, and region, making it easy to track non-conformance trends across different time periods and locations.
2. Number of Tickets By Creation Dates	Total Tickets=COUNT('NCR_page_datacaire_tickets'[id)	The Total Tickets metric counts all ticket IDs in the 'NCR_page_datacaire_tickets' dataset, representing the total number of tickets created. Displayed in a donut chart, where each segment shows the proportion of tickets created by individual users, allowing easy comparison of ticket creation activity by user.
3.Staff and Ticket Metrics Table	Total Tickets=COUNT('NCR_page_datacaire_tickets'[id)	The Staff and Ticket Metrics Table offers a concise overview of ticket and staff-related data. It displays the total number of tickets from the 'NCR_page_datacaire_tickets' dataset, alongside staff metrics from the 'NCR Database Combined' dataset, including Staffed Rostered (scheduled staff), Staff Attended (staff who worked), and Staff on Leave, each defaulting to 0 if no data exists. The table also includes entities (business units) and regions, enabling users to efficiently track ticket volumes and staff activity across different locations and departments.
4. Total NCR Reports by Entity and Region	Total NCR's = COALESCE(DISTINCTCOUNT('NCR_page_datacaire_tickets'[id]), 0)	The Total NCRs metric counts unique Non-Conformance Reports (NCRs) by tallying distinct ticket IDs from the 'NCR_page_datacaire_tickets' dataset, defaulting to 0 if no unique IDs exist. Displayed as an area chart, where the x-axis represents time (e.g., dates), and the y-axis shows the count of unique NCRs. The chart is segmented by entities (business units) and regions, with each area representing the NCR count for a specific entity-region combination, allowing users to visualize trends and compare non-conformance activity across different entities and regions over time.