Discrepancy in Unique Order Count When Aggregating Data Using SUMMARIZE Function

[Sah_123]

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:

dax

Copy code

SUMMARIZE( Tablename, City, Product Line, Sales Batch, "No of Orders", DISTINCTCOUNT(Order ID) )

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.

[Anonymous · Accepted solution]

Hi, @Sah_123 

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.

[bhanu_gautam]

 @Sah_123 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]
)

[Sah_123]

But wanted to keep  dfect code column also in aggregate table. in that case what to do.(in a slicer need to dfect code).