cancel
Showing results for
Did you mean: Frequent Visitor

## GROUP BY with DISTINCT COUNT

I am looking to calculate the unique customer count from the groupby function.

Python code would be :

df = temp_df.groupby(['product','od_pairs']).agg(total_bookings=('booking_no_id', 'count'),
total_teus=('total_teus','sum'),
total_bl=('bl_amount', 'sum'),
total_cost=('cost', 'sum'),
total_customer=('control_party_name', 'nunique'),
)

How do i incorporate distinct count in the groupby function?

summary_table = GROUPBY( export_data, export_data[customer_segment], export_data[product], export_data[main_service], export_data[vessel_direction], export_data[od_pairs],
"booking_count", COUNTX(CURRENTGROUP(), export_data[booking_no_id]),
"total_teus", SUMX(CURRENTGROUP(), export_data[total_teus]),
"total_bl", SUMX(CURRENTGROUP(), export_data[bl_amount]),
"total_cost", SUMX(CURRENTGROUP(), export_data[cost]),
"total_cust", COUNTX(CURRENTGROUP(), DISTINCT(export_data[control_party_name]))

1 ACCEPTED SOLUTION  Super User

Hi @jeanL

I don't believe there's any way to calculate a distinct count using GROUPBY.

Depending where you need to use this table expression, I would recommend rewriting with SUMMARIZECOLUMNS (if you are materializing this table or running as a query) otherwise with SUMMARIZE:

(hopefully I interpreted your intended result correctly):

SUMMARIZECOLUMNS version

``````summary_table =
SUMMARIZECOLUMNS (
export_data[customer_segment],
export_data[product],
export_data[main_service],
export_data[vessel_direction],
export_data[od_pairs],
"booking_count", COUNT ( export_data[booking_no_id] ),
"total_teus", SUM ( export_data[total_teus] ),
"total_bl", SUM ( export_data[bl_amount] ),
"total_cost", SUM ( export_data[cost] ),
"total_cust", DISTINCTCOUNT ( export_data[control_party_name] )
)
``````

``````summary_table =
SUMMARIZE (
export_data,
export_data[customer_segment],
export_data[product],
export_data[main_service],
export_data[vessel_direction],
export_data[od_pairs]
),
"booking_count", CALCULATE ( COUNT ( export_data[booking_no_id] ) ),
"total_teus", CALCULATE ( SUM ( export_data[total_teus] ) ),
"total_bl", CALCULATE ( SUM ( export_data[bl_amount] ) ),
"total_cost", CALCULATE ( SUM ( export_data[cost] ) ),
"total_cust", CALCULATE ( DISTINCTCOUNT ( export_data[control_party_name] ) )
)
``````

Regards,

Owen

Owen Auger

My Blog
2 REPLIES 2 Frequent Visitor

Thanks @OwenAuger ! It works.  Super User

Hi @jeanL

I don't believe there's any way to calculate a distinct count using GROUPBY.

Depending where you need to use this table expression, I would recommend rewriting with SUMMARIZECOLUMNS (if you are materializing this table or running as a query) otherwise with SUMMARIZE:

(hopefully I interpreted your intended result correctly):

SUMMARIZECOLUMNS version

``````summary_table =
SUMMARIZECOLUMNS (
export_data[customer_segment],
export_data[product],
export_data[main_service],
export_data[vessel_direction],
export_data[od_pairs],
"booking_count", COUNT ( export_data[booking_no_id] ),
"total_teus", SUM ( export_data[total_teus] ),
"total_bl", SUM ( export_data[bl_amount] ),
"total_cost", SUM ( export_data[cost] ),
"total_cust", DISTINCTCOUNT ( export_data[control_party_name] )
)
``````

``````summary_table =
SUMMARIZE (
export_data,
export_data[customer_segment],
export_data[product],
export_data[main_service],
export_data[vessel_direction],
export_data[od_pairs]
),
"booking_count", CALCULATE ( COUNT ( export_data[booking_no_id] ) ),
"total_teus", CALCULATE ( SUM ( export_data[total_teus] ) ),
"total_bl", CALCULATE ( SUM ( export_data[bl_amount] ) ),
"total_cost", CALCULATE ( SUM ( export_data[cost] ) ),
"total_cust", CALCULATE ( DISTINCTCOUNT ( export_data[control_party_name] ) )
)
``````

Regards,

Owen

Owen Auger

My Blog Announcements #### Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world. #### Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st! #### What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better. Top Solution Authors
Top Kudoed Authors
Users online (4,560)