ID452 is a Power BI report that reconciles ConnectWise on-hand inventory against e-automate warehouse records at the item level, surfacing sync health, the financial value of discrepancies, and ConnectWise items that have no matching e-automate record at all.
Jump to a specific section by clicking a link
Overview | Samples | Variables | Alert Functionality | Power BI Reporting | KPI Glossary | Related Alerts | Webinar
Click to Subscribe and Download Template
Overview
Overview
If you run both ConnectWise and e-automate, keeping inventory records consistent between the two systems is a constant challenge. Discrepancies between what ConnectWise shows and what e-automate holds can create financial exposure, disrupt billing, and leave stock invisible to your EA system entirely.
ID452 is a Power BI report that reconciles ConnectWise on-hand inventory against e-automate warehouse records at the item level. It gives inventory managers, IT asset coordinators, and finance teams a single dashboard that shows overall sync health, the financial value of discrepancies, where gaps are concentrated by item category, and which ConnectWise items have no matching record in e-automate at all.
Please note: ID452 requires a subscription to ID747 (ConnectWise Customer Module), as this is where the ConnectWise inventory data is sourced from. If ID747 is not running, there will be no data to populate your Power BI report.
⚠ Note: Sync is item-level only. ConnectWise and e-automate each maintain their own warehouse networks, with no mapping between them. Comparisons are always total quantity per item code across all locations in each system, never per warehouse.
The four questions this report answers
- How healthy is the sync? — Overall Sync Rate %, Sync Quality Score, and letter grade.
- What is the financial exposure? — Value at Risk in dollars and as a percentage of EA portfolio value.
- Where are the gaps concentrated? — By item category, direction (EA High vs CW High), and item status.
- What are the orphan items? — CW items with no EA record, including those with physical stock on hand.
Report structure
| Section | Pages | What it covers |
| Sync Health | Sync Scorecard · Item Category Analysis | Overall sync rate, Quality Score, grade, component breakdown, category gap analysis. |
| Item Details & CW Orphans | Item Details · CW Orphan Items | Full item-level reconciliation, and CW items with no EA record. |
| EA & CW Warehouse Position | EA Warehouse Inventory · CW Warehouse Inventory | On-hand quantities by warehouse type, bins, and EA match status. |
Type of Output: On Demand Power BI Report
* * *
Sample
Sample
The Landing Page below is the entry point to the report, grouping the six analytical pages into three sections. Every page in the report is shown and documented individually in the Power BI Reporting section below.
Landing Page
Sync Scorecard
Item Details
CW Orphan Items
* * *
Variables
Variables
None
* * *
Alert Functionality
Alert Functionality
-See here for getting Power BI in place.
-Click here to download the file
You must be subscribed to ID747 for this report to install and run as we push needed support tables.
When downloading template for first time (not subsequent updates/revisions), please allow up to 30 minutes before attempting to refresh to allow our background tables to populate.
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
This section documents every page in the report, in the order it appears on the Landing Page. For the six analytical pages you will find the Purpose, Expected Outcome and KPI content – the same text that appears in the report’s own on-page documentation panel – together with a screenshot of the page.
Navigation
All report pages are accessible from the Landing Page navigation grid. Every analytical page also has a menu icon (≡) in the top-right corner that opens a navigation menu to any other page without returning to the Landing Page.
Data freshness stamps
Two timestamps appear in the top-right corner of every page: Last CW Sync and Last Refresh. Always check both before acting on any sync gaps or financial figures. A stale CW feed invalidates all CW-side comparisons regardless of how recent the EA refresh is.
Report Assumptions & Scope
- Sync is item-level only. CW and EA each have their own warehouse networks with no mapping between them. All comparisons are total quantity per item code across all locations in each system.
- Value at Risk reflects only items with an EA average cost recorded. Items with a $0 average cost contribute zero to financial exposure regardless of their quantity gap. If average cost data is incomplete, Value at Risk understates the true exposure.
- Negative CW quantities are valid data. They reflect CW transactions that over-issued or over-adjusted stock below zero — most commonly in maintenance contract item codes.
- Copilot narrative requires a Microsoft Fabric or Copilot-enabled Power BI capacity. For all other environments, the Sync Health Narrative DAX measure provides equivalent plain-language summaries with no licence requirement.
- Downtime / inactivity is not factored into sync calculations. The report compares current on-hand quantities only — historical movements, open orders, and pending receipts are shown informatively but do not affect the sync rate or score.
Report Pages
Jump to a report page
01 – Landing Page | 02 – Copilot Toggle | 03 – Dictionary / Glossary | 04 – Sync Score & Grade Explanation | 05 – On-Page Documentation | 06 – Filter Panel | 07 – Sync Scorecard | 08 – Item Category Analysis | 09 – Item Details | 10 – CW Orphan Items | 11 – EA Warehouse Inventory | 12 – CW Warehouse Inventory
01 – Landing Page
Purpose: The entry point and navigation hub. It provides orientation before the user engages with any data — what the report measures, how the sync works architecturally, and which analytical pages are available.
Left sidebar
- About This Report — brief description and the item-level sync constraint warning.
- Sync Architecture Warning — an amber callout stating that no warehouse mapping exists between CW and EA, and that all comparisons are total quantity per item code across all locations.
- Documentation / Webinar — link to site documentation and training resources.
- Dictionary / Glossary — link to the in-report KPI glossary page.
- Revision Log — dated list of changes since the initial report release.
Navigation grid
The right panel groups the six analytical pages into three sections, each reachable with a single click: Sync Health, Item Details & CW Orphans, and EA & CW Warehouse Position.
Last CW Sync & Last Refresh timestamps
Both timestamps appear in the top-right corner of every page. Always check both before acting on any sync gaps.
- Last CW Sync — timestamp of the most recent ConnectWise inventory sync. A stale CW feed makes all CW-side comparisons unreliable.
- Last Refresh — timestamp of the most recent EA data load into Power BI.
If the CW Sync timestamp is significantly older than the Last Refresh, the comparison reflects a CW state that may not match current EA data. Investigate the CW sync job before acting on any gaps.
Landing Page
02 – Copilot Toggle
Purpose: The bottom section of the Landing Page contains a Copilot Toggle — a radio button selector that communicates how the narrative text on the Sync Scorecard page is generated, based on whether Microsoft Copilot is enabled in the client’s Power BI environment.
✓ Yes, Copilot is enabled
The Narrative visual leverages Microsoft Copilot to automatically generate intelligent, context-aware text summaries of the report data. It dynamically interprets visuals and produces natural language insights without writing a single line of text manually.
✕ No, Copilot is not enabled
The Sync Health Narrative DAX measure replicates this functionality entirely through DAX with no hardcoded values. It adapts to any client’s item category structure, requires no Fabric or Copilot licence, and updates automatically on every data refresh.
Copilot Toggle
03 – Dictionary / Glossary
Purpose: A dedicated report page that documents every KPI in the Core Measure table. Accessible from the Landing Page sidebar via the Dictionary / Glossary link, and from the Sync Score & Grade Explanation page via the Back to Glossary button.
Search and filter controls
- Search KPI Name — search slicer that filters the glossary table by KPI name.
- Search Definitions — filters by the definition column text.
- Search Calculations — filters by the calculation column text.
- Folder dropdown — filters rows to a specific measure folder (for example Sync KPIs, EA Warehouse Inventory).
The glossary table
Four columns: Folder, KPI Name, Definition, and Calculation (human-readable). A prominent link at the top of the page navigates to the Sync Score & Grade Explanation page for deeper documentation on those two measures.
Dictionary / Glossary
04 – Sync Score & Grade Explanation
Purpose: A dedicated documentation page for the two most complex measures in the report: Sync Quality Score and Sync Quality Grade. The page has four sections: Current Score Breakdown (live visual), Grade Boundaries, Grade & Score Definitions, and The Three Components.
Sync Quality Score
A composite 0–100 index combining three independently weighted pillars. The score updates on every data refresh and adapts to each client’s data automatically.
The three score components
| Component | Max pts | Formula | Explanation |
| Sync Rate | 60 pts (60%) | Sync Rate % × 60 | Linear, 1:1 with sync rate. Carries the most weight because item-level sync rate is the most direct and actionable indicator of data integrity, counting every item equally. |
| Value Risk | 25 pts (25%) | (1 − MIN(VaR ÷ EA Val, 1)) × 25 | Inverse and capped. Scores the full 25 points when Value at Risk is zero, and 0 when VaR equals or exceeds EA portfolio value. Sensitive to average cost data completeness in EA. |
| Orphan Items | 15 pts (15%) | (1 − MIN(OrphanRate × 20, 1)) × 15 | Inverse and capped. A 0% orphan rate scores 15 points. The ×20 multiplier means a 5% orphan rate scores 0. Scales linearly between 0% and 5%. |
Value Risk & average cost: items with no average cost recorded in EA contribute zero to both Value at Risk and EA Value on Hand, regardless of their quantity gap. If average cost data is incomplete, this component understates the true financial exposure.
Sync Quality Grade
Converts the numeric score into a letter grade A–F using fixed boundaries. Both measures always move together — improving the score automatically improves the grade at the relevant threshold.
| Grade | Meaning |
| A: 90 – 100 | Excellent. All three components are healthy. Sync is well-maintained and financial exposure is low. |
| B: 75 – 89 | Good. One component underperforming but not critically. Most items synced. |
| C: 60 – 74 | Acceptable but needs attention. At least one component significantly lagging. |
| D: 45 – 59 | Poor. Multiple components underperforming. Data integrity issues affecting operations. |
| F: 0 – 44 | Critical. Widespread sync failure. Immediate escalation required. |
⚠ Note: Grade boundaries are hard thresholds. Always present both the numeric score and the grade together to avoid misinterpretation at boundary values.
Sync Score & Grade Explanation
05 – On-Page Documentation
Purpose: Every analytical page includes an on-page documentation panel, accessible by clicking the ? icon in the top-right corner of the page header. The panel slides in from the right side of the canvas and contains the page’s Purpose, Expected Outcome, and a KPI list with explanations specific to that page.
Where it is available
On-page documentation is available on all six analytical pages: Sync Scorecard, Item Category Analysis, Item Details, CW Orphan Items, EA Warehouse Inventory, and CW Warehouse Inventory. The Purpose, Expected Outcome and KPI content for every page is reproduced below, so this document and the report itself always say the same thing.
On-Page Documentation
06 – Filter Panel
Purpose: A centralized filter panel is available from every analytical page via the filter icon in the top-right corner. All slicers are organized into four groups.
Item Attributes
Item Status, Item Category, Inventory Code, Equipment Code, Sales Code, Serialized Flag, Item search (code or description), and an Items dropdown.
EA Inventory Attributes
EA Warehouse Status, EA Warehouse Type, EA Warehouse, and an On Hand Qty range slider.
⚠ Note: EA Warehouse filters apply to e-automate warehouse attributes only — these slicers do not affect any item-level quantities or sync calculations.
CW Inventory Attributes
CW Warehouse, CW Warehouse Bin, CW Item Status, CW Item, and a CW Item On-Hand Qty range slider.
⚠ Note: CW Warehouse filters apply to the CW Orphan Items page only — no EA warehouse correlation exists, so these slicers do not affect EA-side quantities.
Sync Attributes
Sync Status, Item Quantities Not Synced, and Variance Direction.
Filter Panel
07 – Sync Scorecard
Section: SYNC HEALTH
Purpose: The flagship executive page. A single-screen answer to four questions leadership needs to answer about the CW–EA sync: how healthy is the sync overall, what is the financial exposure, is the sync direction balanced or systematically skewed, and where are the gaps concentrated by category.
Intentionally broad rather than deep — it delegates item-level and warehouse-level detail to downstream pages via cross-filtering. The Sync Health Narrative card at the bottom provides a plain-language summary that updates on every refresh and works correctly without a Copilot or Fabric licence.
Expected outcome
- Leadership can state the Sync Quality Score, letter grade, and overall sync rate without reading a single table row.
- The score component stacked bar immediately shows which of the three pillars is pulling the score down, directing remediation effort to the correct team.
- The direction breakdown (EA High vs CW High × Active vs Inactive) separates the current action list from the cleanup backlog.
- The category breakdown shows whether gaps are concentrated in one category or spread across many — without requiring any client-specific knowledge to interpret.
- The dynamic narrative provides a shareable summary that adapts to each client’s category structure automatically.
KPIs
| KPI name | Calculation | Definition |
| Total Items | DISTINCTCOUNT(Items[ItemID]) | Total distinct item codes in the model. Denominator context for all sync rate calculations. |
| Items Synced | COUNT where Qty Synced | Items where EA and CW on-hand quantities exactly match. Numerator for Sync Rate %. |
| Items Not Synced | COUNT where Qty Not Synced | Items where EA and CW quantities differ. Key driver of Value at Risk. |
| Sync Rate % | Items Synced ÷ Total Items | Percentage of items with matching quantities. Core health metric. Target: >95%. |
| Sync Quality Score | (SR×60) + (VR×25) + (OI×15) | Composite 0–100 index. Higher = healthier sync. See the Sync Score & Grade Explanation page for the full formula breakdown. |
| Sync Quality Grade | A≥90 / B≥75 / C≥60 / D≥45 / F<45 | Letter grade derived from Sync Quality Score. Converts the index to a format all audiences understand. |
| Items EA High | COUNT where EA qty > CW qty | Items where EA records more stock than CW. Require a CW update or physical count. |
| Items CW High | COUNT where CW qty > EA qty | Items where CW records more stock than EA. Investigate for missing EA receipts or duplicate CW entries. |
| EA Qty on Hand | SUM(Items[ItemOnHandQty]) | Total EA on-hand quantity across all items. Source-of-truth quantity for reconciliation comparisons. |
| EA Value on Hand | SUMX(Items, Qty × AvgCost) | Total EA inventory value. Denominator for Value at Risk % of Total. |
| CW Qty on Hand | SUM(CW inventory qty) | Total CW on-hand quantity. Negative values indicate EA collectively records more quantity than CW across out-of-sync items. |
| CW Orphan Items | COUNT CW Item Status = Orphan w/ Stock | CW items with no EA record and non-zero on-hand quantity. Highest-priority orphans — feeds the Orphan component of Sync Quality Score. |
| Value at Risk | ABS(Value not Synced) | Total absolute dollar value of inventory discrepancies. Always positive — ABS prevents EA High and CW High items from cancelling out. |
| Sync Health Narrative | Dynamic DAX text measure | Fully dynamic plain-text summary of sync health. Adapts to each client’s category structure. No hardcoded values. Replicates Microsoft Copilot narrative without requiring a Fabric licence. |
Sync Scorecard
08 – Item Category Analysis
Section: SYNC HEALTH
Purpose: Breaks the top-level scorecard down to the category level — the most meaningful analytical dimension available in the item master, because it maps to the teams who own the data. All charts are driven dynamically from Items[ItemCategory], so the page works without modification whether a client has two categories or twenty.
The serialized vs non-serialized split is critical because the remediation action differs fundamentally: serialized discrepancies require an asset audit, while non-serialized discrepancies require a stock count or PO review.
Expected outcome
- Identify which item categories contain sync gaps and which are perfectly clean — without needing to know category names in advance.
- Separate asset-tracking problems (serialized) from true stock quantity gaps (non-serialized), directing the correct remediation workflow for each.
- The detail matrix provides a full per-category breakdown, exportable as a starting point for category-owner action plans.
- The Active vs Inactive split confirms whether gaps are in live inventory (highest priority) or retired items (cleanup backlog).
KPIs
| KPI name | Calculation | Definition |
| Categories with Gaps | COUNT cats where Not Synced > 0 | Number of item categories with at least one out-of-sync item. Communicates how concentrated or widespread the sync problem is. |
| Top Gap Category | MAXX by not-synced count | Dynamically identifies the category with the most not-synced items. No category name is hardcoded — adapts to each client’s catalogue. |
| Second Gap Category | 2nd MAXX by not-synced count | The category with the second-highest count of not-synced items. Used alongside Top Gap Category in the page subtitle and narrative. |
| Serialized Not Synced | COUNT where Serialized=TRUE and Not Synced | Serialized items with quantity discrepancies. Typically asset-tracking issues — require a serial number audit, not a bulk stock count. |
| Non-Serialized Not Synced | COUNT where Serialized=FALSE and Not Synced | Non-serialized items with gaps. True quantity discrepancies needing a stock count or PO correction. |
| Active Items Not Synced | COUNT where Status=Active and Not Synced | Active items with gaps. These affect live service cost calculations, reorder triggers, and billing — highest remediation priority. |
| Sync Rate % by Category | Synced ÷ Total per category | Sync rate within each item category filter context. Sorted ascending (worst first) so problem categories always appear at the top regardless of their names. |
| EA High / CW High by Category | COUNT per direction per category | Directional split within each category. Identifies whether the CW team or the EA receiving team needs to act on each category’s gaps. |
Item Category Analysis
09 – Item Details
Section: ITEM DETAILS & CW ORPHANS
Purpose: The operational workhorse page. Every out-of-sync item is surfaced with all the fields required for an inventory clerk or IT asset manager to take corrective action without leaving the report: item code, description, category, serialized flag, item status, EA qty, CW qty, signed qty difference, signed value difference, variance direction, sync status, and exposure rank.
Sorted by Value Diff $ descending by default, so the highest financial exposures appear first. The Sync Status column provides four-way triage so users know exactly what action to take for each item.
Sync Status values
| Status | Action required |
| EA High — Action Needed | EA records more quantity than CW. The CW team needs to update records or run a physical count. |
| CW High — Investigate | CW records more quantity than EA. Investigate for missing EA receipts, unrecorded returns, or duplicate CW entries. |
| Inactive — Review | Item is inactive in EA but still has a quantity discrepancy. Lower priority — review for write-off or archival. |
| Synced | EA and CW quantities match. No action required. |
KPIs
| KPI name | Calculation | Definition |
| Value at Risk | ABS(Value not Synced) | Total absolute dollar value of inventory discrepancies in the current filter context. Always positive. |
| Items EA High | COUNT where Direction = EA High | Items where EA qty exceeds CW qty. Need a CW correction or physical count. |
| Items CW High | COUNT where Direction = CW High | Items where CW qty exceeds EA qty. Need an EA investigation. |
| Qty Diff | ABS(EA qty − CW qty) per item | Always positive — magnitude of the quantity gap regardless of direction. |
| Value Diff $ | Qty Diff × Item Avg Cost per item | Dollar value of the quantity gap per item. Zero for items with no EA average cost recorded. |
| Direction | EA High / CW High / Synced per item | Direction of the variance per item. The first decision point in any remediation workflow. |
| Sync Status | 4-way status per item | Operational triage label. The label text is itself an instruction — no documentation needed to know what action to take. |
| Exposure Rank | RANKX by abs value variance DESC | Dense rank: rank 1 = highest financial exposure in the current filter context. Default sort column. Updates correctly when slicers are applied. |
Item Details
10 – CW Orphan Items
Section: ITEM DETAILS & CW ORPHANS
Purpose: Dedicated to items that exist in ConnectWise but have no matching record in e-automate. These items are invisible to EA’s billing engine, min/max replenishment calculations, service cost reporting, and parts consumption tracking.
The page separates actionable orphans (non-zero CW on-hand quantity — physical stock EA cannot see) from zero-quantity orphans (a data cleanup task with no immediate operational impact). A CW warehouse distribution chart shows where the physical stock is located — valid on this page because it shows CW-only data.
⚠ Note: Filters on this page operate on ConnectWise warehouse inventory only. No EA warehouse correlation exists.
Expected outcome
- The EA administrator sees how many distinct CW item codes have no EA record, and of those, how many have physical stock that needs to be accounted for.
- The CW warehouse distribution chart identifies which physical locations contain the highest concentration of orphan stock, guiding where to start the audit.
- The full orphan item table provides the exact CW item codes needed to create missing EA item records or retire CW entries.
- Tracking the orphan rate over successive snapshots confirms whether the cleanup effort is reducing the problem or whether new orphans are being created.
KPIs
| KPI name | Calculation | Definition |
| Items missing from e-automate | COUNT where No EA match | Distinct CW item codes with no matching EA record. Includes zero-quantity items (data cleanup) and non-zero-quantity items (operational risk). |
| Orphan Items with Stock (Qty ≠ 0) | COUNT No EA match AND qty ≠ 0 | Orphan items with non-zero CW on-hand quantity. Physical stock EA cannot track, bill, or plan replenishment for. Highest-priority orphans. |
| Orphan Items Qty | SUM CW qty for orphan items | Total CW on-hand quantity across all orphan items. The absolute value communicates the physical scale of the orphan problem. |
| CW Items with No EA Match % | Orphan qty≠0 ÷ Total CW rows | Orphan rate as a percentage of total CW inventory rows. Used to track orphan cleanup progress over time, and feeds 15 points of Sync Quality Score. |
CW Orphan Items
11 – EA Warehouse Inventory
Section: EA & CW WAREHOUSE POSITION
Purpose: A single-screen view of how on-hand inventory is distributed across EA’s warehouse network. Because EA has no bin column, the deepest analytical grain is warehouse + item. Designed for inventory managers and EA administrators who need to understand the physical distribution of stock as recorded in e-automate before taking any cross-system comparison into account.
Expected outcome
- The inventory manager sees at a glance which warehouse types and individual warehouses hold the most on-hand stock, without opening EA directly.
- The Active vs Inactive item donut confirms whether stock is concentrated in live items or sitting in retired items — flagging inactive items with quantity as a data quality cleanup task.
- The Serialized vs Non-Serialized donut informs how a stock count would need to be structured: unit-level asset tracking vs bulk quantity count.
- The item list provides every warehouse–item combination with on-hand value, allocated, ordered, and defective quantities — filterable by warehouse type, status, and item attributes.
EA Warehouse types
| Type | What it holds |
| Company Site | Central branch stock. Typically the largest on-hand quantity pool. |
| Salesrep | Field sales representative consignment stock. |
| Technician | Field engineer van stock. Quantity may be zero if techs do not carry inventory. |
| Drop-ship | Vendor-direct shipments. Quantity may be zero. |
| Fixed Asset | Fixed assets tracked within the EA inventory module. |
KPIs
| KPI name | Calculation | Definition |
| EA Warehouses | DISTINCTCOUNT(Warehouse) | Total distinct EA warehouse names in the current filter context. |
| Active Warehouses | COUNT where Status = Active | Active EA warehouses. Primary operational stock locations. |
| Inactive Warehouses with Stock | COUNT Inactive and Qty > 0 | Data quality flag. Retired warehouses should carry zero on-hand quantity. |
| Items with Stock | DISTINCTCOUNT where OnHandQty > 0 | Distinct item codes with at least one unit on hand across EA warehouses. |
| On Hand Qty | SUM(OnHandQty) | Total on-hand quantity across all EA warehouse rows in the current filter context. |
| On Hand Value | SUMX(OnHandQty × RELATED(AvgCost)) | Total inventory value. For each warehouse row: on-hand qty × item average cost, summed. |
| Available Qty | OnHand − Allocated − Defective | True free-to-use quantity: physically present, in good condition, and uncommitted to any order. |
| Allocated Qty | SUM(Allocated) | Stock committed to open service orders or sales orders but not yet consumed. |
| Ordered Qty | SUM(Ordered) | Stock on open purchase orders not yet received. |
| Defective Qty | SUM(DefectiveQty) | On-hand but unusable stock. Non-zero values flag warehouses with unprocessed defective returns. |
| Back Ordered Qty | SUM(BackOrdered) | Demand that cannot be fulfilled from current stock. |
| Serialized / Non-Serialized split | COUNT by Items[ItemSerialized] | Drives the serialized vs non-serialized donut. Tells the team whether the EA inventory is primarily tracked at unit level (serialized) or by count (non-serialized). |
EA Warehouse Inventory
12 – CW Warehouse Inventory
Section: EA & CW WAREHOUSE POSITION
Purpose: A single-screen view of inventory as ConnectWise records it — at the warehouse and bin level. Unlike the EA page, CW has bin-level data available, enabling a deeper physical location drill.
The page highlights three CW-specific operational concerns not available on the EA side:
- Items with no EA match (orphans) — CW items invisible to EA.
- Negative on-hand quantities — data quality flags where more units were issued or adjusted out than were ever received in CW.
- EA match rate per warehouse — shows which locations have the highest orphan concentration, directing where to focus the EA admin team’s record creation effort.
⚠ Note: CW and EA warehouse networks are separate and not mapped to each other. This page shows CW-only data. Filtering by CW Warehouse does not affect any EA-side measures or sync calculations.
Expected outcome
- The CW administrator sees which warehouses and bins hold actual positive on-hand stock — separating the small number of live rows from the large volume of zero-quantity catalogue entries.
- Negative quantity rows are surfaced as data quality flags — each represents a CW transaction that over-issued or over-adjusted stock below zero.
- The EA match rate bar chart (sorted ascending) immediately shows which warehouses have the lowest match rate, directing the EA admin team’s orphan cleanup effort.
- The item list surfaces every row with actual stock (positive qty), its bin location, last updated timestamp, updated-by user, and EA match status — everything needed to physically locate and reconcile the item.
KPIs
| KPI name | Calculation | Definition |
| CW Warehouses | DISTINCTCOUNT(CWWarehouseID) | Distinct CW warehouse count. Different from EA warehouse count — no mapping between the two systems exists. |
| Distinct Bins | DISTINCTCOUNT(CWWarehouseBin) | Distinct bin names across all CW warehouses. The most granular physical location identifier available in CW. |
| On Hand Qty | SUM(CWItemOnhandQty) | Total CW on-hand quantity (signed). Negative values reflect CW transactions where more units were issued than received — typically maintenance contract items. |
| CW Value on Hand | SUM(CWItemOnHandValue) | Total CW inventory value using CW item values. May be negative if dominated by negative-quantity positions. |
| Items with Stock | COUNT where CWItemOnhandQty > 0 | CW rows with positive on-hand quantity. The actionable physical stock universe — typically a small subset of total CW rows. |
| Items missing from EA | COUNT distinct CWItem where No EA match | Distinct CW item codes with no matching EA record. The full orphan catalogue regardless of quantity. |
| Orphan Items with Stock | COUNT No EA match AND qty ≠ 0 | Orphan items with non-zero CW on-hand quantity. Physical stock EA cannot see — immediate action required. |
| CW Items with No EA Match % | Orphans with qty≠0 ÷ Total CW rows | Orphan rate as a percentage of total CW inventory rows. Primary metric for tracking orphan cleanup progress. Feeds the Orphan Items component of Sync Quality Score. |
| CW Item Status | In EA / Orphan no Stock / Orphan w/ Stock | Three-way classification per CW row. Orphan w/ Stock = red (immediate action). In EA = green (no action needed). Orphan no Stock = amber (cleanup backlog). |
CW Warehouse Inventory
* * *
KPIs Glossary
KPIs Glossary
All primary KPIs grouped by their measure folder in the Core Measure table. For full DAX expressions, refer to the in-report Dictionary / Glossary page.
Sync KPIs
| KPI name | Calculation | Definition |
| Sync Rate % | Items Synced ÷ Total Items | Percentage of items where EA and CW on-hand quantities exactly agree. Core health metric. Target: >95%. |
| Value at Risk | ABS(Value not Synced) | Total absolute dollar value of inventory discrepancies. Always positive. |
| Value at Risk % of Total | VaR ÷ EA Value on Hand | VaR as a percentage of EA portfolio value. Contextualises the absolute figure against the client’s inventory size. |
| Sync Quality Score | (SR×60) + (VR×25) + (OI×15) | Composite 0–100 index combining sync rate, value risk, and orphan rate. The single trendable number for leadership. |
| Sync Quality Grade | A≥90 / B≥75 / C≥60 / D≥45 / F<45 | Letter grade derived from Sync Quality Score. |
| CW Items with No EA Match % | Orphan rows (qty≠0) ÷ Total CW rows | Orphan rate. Feeds 15 points of Sync Quality Score. Track over successive snapshots to measure cleanup progress. |
| Avg Variance per Out-of-Sync Item | ABS(Qty not Synced) ÷ Items Not Synced | Average quantity gap per not-synced item. High = problem concentrated in few items; low = many items slightly off. Guides remediation strategy. |
| Snapshot Date | INT(Last Refresh) | Date-only version of the Last Refresh timestamp. Use to stamp exported rows when building a manual trend table to track Sync Quality Score over time. |
Score Components
| KPI name | Calculation | Definition |
| Score Component — Sync Rate | Sync Rate % × 60 | Sync Rate’s contribution to Sync Quality Score. Maximum 60 points. Linear — a 100% sync rate earns the full 60 points. |
| Score Component — Value Risk | (1 − MIN(VaR ÷ EAVal, 1)) × 25 | Value Risk’s contribution. Maximum 25 points. Scores 0 when VaR equals or exceeds EA portfolio value. Sensitive to average cost completeness. |
| Score Component — Orphan Items | (1 − MIN(OrphanRate × 20, 1)) × 15 | Orphan rate’s contribution. Maximum 15 points. Scores 0 at ≥5% orphan rate. Scales linearly between 0% and 5%. |
Sync Detail
| KPI name | Calculation | Definition |
| Total Items | DISTINCTCOUNT(ItemID) | Total distinct item codes in the model. |
| Items with Qty Synced | COUNT where Qty Synced | Numerator for Sync Rate %. |
| Items with Qty Not Synced | COUNT where Qty Not Synced | Total out-of-sync item count. |
| Items EA High | COUNT where Direction = EA High | Items where EA qty exceeds CW qty. CW correction needed. |
| Items CW High | COUNT where Direction = CW High | Items where CW qty exceeds EA qty. EA investigation needed. |
| Active Items Not Synced | COUNT Active and Not Synced | Highest-priority remediation queue. Live inventory with quantity gaps. |
| Inactive Items Not Synced | COUNT Inactive and Not Synced | Lower-priority cleanup backlog. Retired items with quantity discrepancies. |
| Serialized Items Not Synced | COUNT Serialized and Not Synced | Asset-tracking issues requiring a serial number audit, not a bulk stock count. |
| Non-Serialized Items Not Synced | COUNT Non-Serialized and Not Synced | True physical inventory gaps. Require a stock count or PO review. |
| Total Categories | COUNT distinct non-blank ItemCategory | Total distinct item categories in the model. Fully dynamic — adapts to each client’s catalogue. |
| Categories with Gaps | COUNT cats where Not Synced > 0 | Categories containing at least one out-of-sync item. |
| Categories Perfectly Synced | COUNT cats where Not Synced = 0 | Complement of Categories with Gaps. Every item in these categories matches exactly. |
| Top Gap Category | MAXX by not-synced count | Name of the category with the most out-of-sync items. Fully dynamic — no hardcoding. |
| Second Gap Category | 2nd MAXX by not-synced count | Name of the second-highest gap category. Fully dynamic. |
| Net Value Variance (Signed) | SUM(Value Diff Signed) | Signed total dollar variance. Positive = EA overstates vs CW. Used for balance sheet reconciliation. |
Exposure Analysis
| KPI name | Calculation | Definition |
| Item Value Variance (Abs) | ABS(SUM(Value Diff)) per item | Per-item absolute dollar variance. Always positive — the primary sort metric for the Pareto chart. |
| Exposure Rank | RANKX(ALLSELECTED, Value Variance, DESC, DENSE) | Dense rank: 1 = highest financial exposure in the current filter context. Blank for zero-variance items. ALLSELECTED ensures rank updates correctly when slicers are applied. |
| Cumulative Exposure % | Cumulative Exposure ÷ Total VaR | Running percentage of total Value at Risk from the top-ranked item downward. Reaches 100% at the last item. The 80% crossing point identifies the critical minority. |
| Is Top 80% Exposure | If Cumulative % ≤ 80% → "Top 80%" | Classifies each item for Pareto bar chart colouring. "Top 80%" = orange bars; "Remaining 20%" = grey bars. |
| Variance Direction | EA High / CW High / Synced per item | Direction of each item’s variance. The first decision point in any remediation workflow. |
| Variance (Abs) — Top 80% | Value Variance if Top 80%, else BLANK() | Orange bar series on the Pareto chart. Returns BLANK for Remaining 20% items so no bar is rendered. |
| Variance (Abs) — Remaining 20% | Value Variance if Remaining 20%, else BLANK() | Grey bar series on the Pareto chart. Paired with the Top 80% measure to create the dual-colour Pareto without a Column Series field. |
Direction & Status Breakdown
| KPI name | Calculation | Definition |
| Items EA High Active | COUNT EA High and Active | Active items where EA qty > CW qty. Highest-priority correction queue. |
| Items CW High Active | COUNT CW High and Active | Active items where CW qty > EA qty. Investigate for missing EA receipts. |
| Items EA High Inactive | COUNT EA High and Inactive | Inactive items with an EA-high discrepancy. Review for write-off. |
| Items CW High Inactive | COUNT CW High and Inactive | Inactive items with a CW-high discrepancy. Review for disposal or archival. |
| Items EA/CW High Active/Inactive (Neg) | Count × −1 | Four negative-integer variants used as bar values on diverging bar charts. The negative sign is a chart display convention only — it makes bars extend in opposite directions to show active vs inactive proportions side by side. |
Narrative
| KPI name | Calculation | Definition |
| Sync Health Narrative | Dynamic DAX CONCATENATEX text | Fully dynamic plain-text summary of sync health across four paragraphs: overall totals, top gap categories (dynamically identified), perfectly synced categories, and Sync Quality Score explanation. No category names, thresholds, or counts are hardcoded. Adapts to any client’s catalogue structure at runtime. Replicates Microsoft Copilot narrative without requiring a Fabric or Copilot licence. |
Sync Score Breakdown
| KPI name | Calculation | Definition |
| Score Breakdown — Grade Label | [Sync Quality Grade] | Alias of Sync Quality Grade for the large letter display in the Score Breakdown visual left panel. |
| Score Breakdown — Score Label | FORMAT(Score, "0.0") & " / 100" | Formatted score string (for example "69.3 / 100") displayed below the grade letter. |
| Score Breakdown — Sync Rate Bar % | Score Component — Sync Rate ÷ 60 | Sync Rate component as a 0–1 proportion of its 60-point maximum. Drives the filled width of the Sync Rate progress bar. |
| Score Breakdown — Sync Rate Label | FORMAT(Score, "0.0") & " / 60" | Formatted label shown to the right of the Sync Rate bar (for example "55.7 / 60"). |
| Score Breakdown — Value Risk Bar % | Score Component — Value Risk ÷ 25 | Value Risk component as a 0–1 proportion of its 25-point maximum. Drives the filled width of the Value Risk progress bar. |
| Score Breakdown — Value Risk Label | FORMAT(Score, "0.0") & " / 25" | Formatted label shown to the right of the Value Risk bar (for example "0.0 / 25"). |
| Score Breakdown — Orphan Bar % | Score Component — Orphan Items ÷ 15 | Orphan Items component as a 0–1 proportion of its 15-point maximum. Drives the filled width of the Orphan bar. |
| Score Breakdown — Orphan Label | FORMAT(Score, "0.0") & " / 15" | Formatted label shown to the right of the Orphan Items bar (for example "13.6 / 15"). |
| Score Breakdown — Summary Text | Dynamic DAX sentence | One-line dynamic summary: Sync Rate % (Synced/Total) · Value at Risk vs EA portfolio (%) · Orphan rate % (orphan rows / CW rows). All values are dynamic measures — no numbers hardcoded. |
| Score Breakdown — [Color] measures | SWITCH on score thresholds | Three bar fill colours and three label font colours. Orange = partial score, green = healthy (≥80% of max), dark grey = zero. Drive Power BI conditional formatting via Field Value on the progress bar visual. |
* * *
Related Alerts
Related Alerts
ID747 - ConnectWise Customer Module — ID452 sources all of its ConnectWise inventory data from ID747. *Required
ID817 - ConnectWise Purchase Order Sync
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
**COMING SOON**
0 Comments