Three order lines do not necessarily represent three orders. If an order contains several products, deleting the repeated order ID would discard legitimate detail. Keep the line records and choose the aggregation that answers the report's question.

Prepare a disposable table with OrderID and Category. Use three rows: A/Cables, A/Adapters and B/Cables. There are three lines, two distinct orders overall, two orders appearing in Cables and one in Adapters. Write these expectations before creating the report.

Build the report with the required model

In desktop Excel for Windows, select the practice table and choose Insert, PivotTable. Select Add this data to the Data Model and place the report on a new worksheet. Add Category to Rows and OrderID to Values. Open the value field's Value Field Settings and select Distinct Count under Summarize Values By.

Microsoft defines Distinct Count as the number of unique values and requires the Data Model for it. Ordinary Count counts nonempty values instead. If Distinct Count is unavailable, check how the PivotTable was created; changing its number format cannot supply the missing aggregation.

For the practice data, inspect Cables, Adapters and Grand Total. Expect 2, 1 and 2. Summing the displayed category counts gives 3, but that sum counts order A once in each category. The grand distinct result answers how many different orders appear across the whole selected set.

Explain the total before changing it

Do not replace that grand total with the sum of the category values merely to make the report look additive. Ask whether the reader wants orders, order lines, or order-category memberships. Those are different counting units, even though all can be expressed as whole numbers.

Keep a small reconciliation beside the report: Cables contains A and B; Adapters contains A; their union contains A and B. This makes the overlap visible without requiring a reader to infer the counting rule from the field caption.

For the real data, settle missing order IDs and the intended identity rule before counting. A report cannot recover an order identity that the source does not provide. Retain genuine multi-line orders and compare a few selected groups against their actual IDs.

Label the output Unique orders and explain that categories can overlap when that affects interpretation. Acceptance means both the category memberships and the overall set agree with the source records, while all authorised line detail remains available for its other uses.

Sources: Microsoft: PivotTable summary functions; Microsoft: supporting procedure.