It is frustrating to see a simple request for distinct counts turn into a logic puzzle that defeats the tool. The user's confusion is completely justified: sixteen rows of products with only six unique values should return a distinct count of six, not sixteen. That the pivot table returns the total row count instead tells us something important about the gap between what software promises and what it actually delivers.
The problem is not the user's understanding of data. It is the hidden complexity of the "Add this data to the Data Model" feature in Excel. When a user checks that box, they are not just creating a pivot table. They are building a cube inside the workbook. That cube does not automatically treat text columns as dimensions suitable for distinct counting the way a human would expect. The feature requires the user to know that the data model's engine, Power Pivot, processes distinct counts differently than a standard pivot table. The user followed the visible steps correctly, but the tool did not surface the invisible prerequisite: you must ensure the column is formatted as a text data type in the model, not just in the worksheet. That is a design failure, not a user error.
This is the core issue with legacy spreadsheet software. It asks users to think like programmers to accomplish what should be a basic analytical task. The user should not have to troubleshoot a data model to count unique products. They should be able to say "give me the distinct products" and trust the result. When the tool breaks that trust, it shifts the cognitive load onto the person who is already doing the work. The result is wasted time, eroded confidence, and a lingering suspicion that the software is working against you rather than for you.
For anyone who has felt this friction, the takeaway is straightforward: the tool you are using was built for a world where data was small and static. Your world is not that world anymore. The solution is not to learn more workarounds. It is to explore tools that treat distinct counts as a native, transparent operation, tools that let you ask a question in plain terms and get the correct answer on the first try. You should not have to debug a pivot table to find out that sixteen is actually six.