Forum Discussion
Discrepancy in Unique Order Count When Aggregating Data Using SUMMARIZE Function
Hi everyone,
I'm working with a large dataset and am encountering a discrepancy in the unique order count when aggregating the data. Here’s a summary of the issue:
Original Data:
I have a table with the following columns: Order ID, Defect Code, Product Line, Sales Batch, Month Difference, and City. Here is a sample of the data:
Order ID Defect Code Product Line Sales Batch Month Difference City| JC-000249 | QB00 | H | Apr-21 | 2 | AA |
| JC-000249 | QB40 | H | Apr-21 | 2 | AA |
| JC-001359 | QB40 | H | Apr-21 | 1 | AA |
| JC-001396 | QB40 | H | Apr-21 | 1 | AA |
| JC-002008 | QB40 | H | Apr-21 | 2 | AA |
| JC-003005 | AV19 | H | Apr-21 | 3 | AA |
| JC-003005 | AV19 | H | Apr-21 | 3 | AA |
| JC-003005 | AV19 | H | Apr-21 | 3 | AA |
| JC-M000825 | QB40 | H | Apr-21 | 1 | AA |
Expected Results:
- Unique Order Count: By city, product line, and sales batch level, I expect a total of 6 unique orders.
Aggregation Using SUMMARIZE Function:
I used the SUMMARIZE function to aggregate the data as follows:
Aggregated Output:
The output I received was:
Product Line City Sales Batch Month Difference Defect Code No of Orders| H | AA | Apr-21 | 1 | QB40 | 3 |
| H | AA | Apr-21 | 2 | QB00 | 1 |
| H | AA | Apr-21 | 2 | QB40 | 2 |
| H | AA | Apr-21 | 3 | AV19 | 1 |
Issue:
When calculating the unique order count at the city level, the result is 7, whereas I expected it to be 6. The discrepancy arises due to the aggregation.
Question:
How can I correctly aggregate the data to ensure that the unique
order count matches the expected results? Is there a specific approach or adjustment I need to make to the SUMMARIZE function or the aggregation logic to account for this discrepancy?
Thank you in advance for your help!
Feel free to adjust the details as needed to fit your specific context.
Hi, Anonymous
You can try the following methods.
Measure = Var _table=SUMMARIZE( 'Table', 'Table'[City], 'Table'[Product Line], 'Table'[Sales Batch Month], 'Table'[Order ID] ) RETURN COUNTX(_table,[Order ID])Is this the result you expected?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Anonymous The discrepancy in the unique order count arises because the SUMMARIZE function is aggregating the data at a more granular level than you intend.
Try using
SUMMARIZE(
Tablename,
City,
Product Line,
Sales Batch,
"No of Orders", DISTINCTCOUNT(Order ID)
)Then
VAR AggregatedTable =
SUMMARIZE(
Tablename,
City,
Product Line,
Sales Batch,
"No of Orders", DISTINCTCOUNT(Order ID)
)RETURN
SELECTCOLUMNS(
AggregatedTable,
"City", [City],
"Product Line", [Product Line],
"Sales Batch", [Sales Batch],
"No of Orders", [No of Orders]
)Hi, Anonymous
You can try the following methods.
Measure = Var _table=SUMMARIZE( 'Table', 'Table'[City], 'Table'[Product Line], 'Table'[Sales Batch Month], 'Table'[Order ID] ) RETURN COUNTX(_table,[Order ID])Is this the result you expected?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
