Warehouse operations data structure for effective multi client dashboard & visualization guidance

Hi 3PLFORALL ,

Thank you for reaching out to the Microsoft Community Forum.

Instead of separating data by unit type like volume, accuracy and time, consider a normalized structure that allows for flexibility and easier visualization.

Please try below schema.

1. Clients Table (ClientID, ClientName, Active).

2. Deliverables Table (DeliverableID, KPI Name, Category, UnitType).

3. ClientDeliverables Table (ClientID, DeliverableID).

4. KPI Data Table (EntryID, ClientID, DeliverableID, Date, TargetValue, ActualValue, KPIResult).

Please try below modeling steps.

1. Link Clients, Deliverables, and KPI Data via ClientID and DeliverableID. This enables filtering by client, category, and deliverable.

2. Add a UnitType column in the Deliverables table. Use DAX to format visuals based on unit type (show % for accuracy, hours for time).

3. Use slicers for Client, Category, KPI Name and Date Range. Create drill-through pages for detailed KPI views.

4. Create DAX measures for KPI Met Count and KPI Not Met Count . please refer below sample measures.

KPI Met Count = COUNTROWS(FILTER(KPIData, KPIData[KPIResult] = "Met"))

KPI Not Met Count = COUNTROWS(FILTER(KPIData, KPIData[KPIResult] = "Not Met"))

5. Use switch logic in DAX for handling different unit formatting like display % for accuracy KPI's.

6. Create calculation groups for dynamic metric switching to show Actual and target.

7. Avoid using wide tables with separate columns for every metric.

Note: Add a “KPI Weight” column to prioritize certain KPIs in rollups. Add a “Threshold” column to dynamically calculate KPI result (Met/Not Met) using DAX.

Please try below visuals.

1. Matrix table (Rows: KPI Name, Columns: target, Actual and Result).

2. KPI card (Show Met and Not Met status with a color indicator per KPI).

3. Bar/ Line charts (To Compare actual VS Target for selected KPI's).

Dinesh