Forum Discussion
Warehouse operations data structure for effective multi client dashboard & visualization guidance
Hello,
Hoping to get some guidance on what the appropriate way for me to format my source data which will be warehouse operations data, for multiple clients with each of the clients having standard deliverables that will be tracked for each client, with some clients having additional deliverables that would be tracked in addition to the standard deliverables. The data will be entered daily by warehouse staff.
As mentioned above, each client would have the same standard set of deliverables that the warehouse staff would be inputting data for. While some clients can potentially have additional deliverables that would be tracked. Some of the deliverables have different measurement units, such as number of inbound pallets (volume), inbound orders that were receipt to stock in 24 hours (percentage), and outbound shipment accuracy (accuracy %).
I have attempted to create mock data & pull them into Power BI but ran into issues - mainly with some of the visualizaitons wanting to incorporate the data as "Count of" in the visualization which is not really what I am looking for. I am looking to be able to see what was the target and what are the actual KPI values, per client, per category (inbound, outbound, inventory management, and operations QA) where I could drill down or create a slicer to see the info for specific deliverables in a specific category, then perhaps maybe a roll up of all clients.
Below is an example of the data that I would like to be tracking per client then - I have broken them into different categorical tables based on the units associated with the deliverable in each (volume, accuracy, and time) as it seemed that I might run into difficultity with the visualizations in Power BI when having a table with multiple deliverables with different units. Would love to see what different source data ideas the community has or if someone wants to try throwing this data into a dashboard I would be happy to take a look at that as well.
Volume Data
| Data Category | KPI Name | Date | Target KPI | Actual KPI | KPI Result |
| Outbound | Total Purchase Orders Received | 7/3/2025 | 1 | 2 | KPI Met |
| Outbound | Lines Per Order | 7/3/2025 | 4 | 3 | KPI Not Met |
| Outbound | Total Outbound Lines | 7/3/2025 | 1 | 1 | KPI Met |
| Outbound | Total Orders Shipped (Same Day) | 7/3/2025 | 0 | 0 | KPI Met |
| Outbound | Total Rollover Orders Not Shipped | 7/3/2025 | 0 | 0 | KPI Met |
| Outbound | Total Orders Shipped (Same Day) | 7/3/2025 | 0 | 0 | KPI Met |
| Inbound | Total Inbound Receipts | 7/3/2025 | 2 | 3 | KPI Met |
| Inbound | Total Inbound Lines | 7/3/2025 | 8 | 8 | KPI Met |
| Inbound | Quantity of NCM Received | 7/3/2025 | 0 | 0 | KPI Met |
| Inbound | Open NCM Pending Disposition | 7/3/2025 | 0 | 0 | KPI Met |
| Inbound | Number of Pallets Not Processed | 7/3/2025 | 0 | 0 | KPI Met |
| Inventory | Empty Bins with Product | 7/3/2025 | 0 | 0 | KPI Met |
| Inventory | Locations in Error | 7/3/2025 | 0 | 0 | KPI Met |
| Inventory | 2nd Location Count in Error | 7/3/2025 | 0 | 0 | KPI Met |
| Inventory | Cycle Count Adjustments | 7/3/2025 | 0 | 0 | KPI Met |
| Inventory | On Hold Count Discrepancy | 7/3/2025 | 0 | 0 | KPI Met |
| Inventory | Absolute Units in Error | 7/3/2025 | 0 | 0 | KPI Met |
| Operations QA | Number of Non Conforming | 7/3/2025 | 0 | 0 | KPI Met |
Time Data
| Data Category | KPI Name | Date | Target KPI | Actual KPI | KPI Result |
| Inbound | Client Acknowledgement of Non Conforming Materials | 7/3/2025 | 24 hours | 24 hours | KPI Met |
| Inbound | Resolution of Overage, Shortage, or Damage | 7/3/2025 | 24 hours | 24 hours | KPI Met |
Accuracy Data
| Data Category | KPI Name | Date | Target KPI | Actual KPI | KPI Result |
| Outbound | Fill Rate | 7/3/2025 | 100% | 100% | KPI Met |
| Outbound | Outbound Shipment Accuracy | 7/3/2025 | 100% | 100% | KPI Met |
| Inbound | Dock To Stock (24 hour) | 7/3/2025 | 100% | 99% | KPI Not Met |
| Inventory | Client Ocupied BINs Counted | 7/3/2025 | 5% | 5% | KPI Met |
| Inventory | Location Accuracy | 7/3/2025 | 100% | 100% | KPI Met |
| Inventory | Absolute Piece Accuracy % | 7/3/2025 | 100% | 100% | KPI Met |
| Operations QA | Shipment Accuracy | 7/3/2025 | 100% | 100% | KPI Met |
Thank you in advance for any input and guidance you can provide to me on this!
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).
I hope this information helps. Please do let us know if you have any further queries.
Regards,
Dinesh
6 Replies
Please include, in a usable format, not an image, a small set of rows for each of the tables involved in your request. The subset of rows you provide, even is just a subset of the original tables, must cover your issue or question completely.
Need help uploading data? click here
Want faster answers? click here
Thank you I have updated the tables following the details provided in the first link.
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).
I hope this information helps. Please do let us know if you have any further queries.
Regards,
Dinesh
Hi 3PLFORALL ,
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.
Regards,
Dinesh
Hi @3PLFORALL ,
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.
Regards,
Dinesh
