A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data
Hi @Noah S,
Thanks for sharing your workbook structure. It looks like a solid starting point for building the dashboard. Based on my experience working with Excel dashboards and PivotTables, here are a few suggestions for your three questions:
1/Counting each Order ID only once in Total Orders:
When creating the PivotTable, select Add this data to the Data Model. Then:
- Add Order ID to the Values area.
- Open Value Field Settings.
- Under Summarize Values By, select Distinct Count
This counts each Order ID once, even when the same order contains multiple product lines. The Distinct Count option is available when the PivotTable uses the Data Model.
For your reference: Change the summary function or custom calculation for a field in a PivotTable.
Alternatively, you can create a reusable measure:
= DISTINCTCOUNT(Sales[Order ID])
For detailed steps, see Measures in Power Pivot.
2/Using a shared Data Model as the source for all PivotTables:
For this dashboard, I recommend using one source table and one shared Data Model for all PivotTables and PivotCharts. This supports distinct counts, reusable measures, table relationships, and a consistent source for the entire dashboard.
Create all PivotTables from the same Data Model. You can then use Slicer > Report Connections to connect a slicer to multiple PivotTables that share the same data source.
See Create a Data Model in Excel.
3/Adding new data without breaking PivotTables and charts:
Convert the source data into an Excel Table rather than using a fixed range such as A1:K5000. Keep one row per product transaction, use consistent column headers and data types, and avoid blank headers, merged cells, or manually added subtotal rows.
When you add new records directly below the table, the table expands automatically. You can then use Data > Refresh All to refresh the data connections and PivotTables, which will update the associated PivotCharts and dashboard.
Please refer: Refresh PivotTable data.
If the sales data is imported regularly from CSV files, reports, databases, or other workbooks, consider using Power Query to import and clean the data before loading it into the Data Model.
You may also consider adding a separate Calendar table for Year, Quarter, Month, and Year-Month filtering. This will make date filtering and future calculations such as year-to-date or month-over-month comparisons easier to manage.
With this setup, you should be able to append new sales records and use Refresh All without rebuilding the dashboard.
These are based on my own experience, and I hope they give you some useful references for building a dashboard that is easier to refresh, maintain, and scale as your data grows.
Thank you for taking the time to read my suggestions.
If the answer is helpful, please kindly click "Yes" button below. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in the forum documentation to enable e-mail notifications if you want to receive the related email notification for this thread.