cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jeanL
Regular 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
OwenAuger
Super User
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] )
)

 

ADDCOLUMNS/SUMMARIZE version

summary_table =
ADDCOLUMNS (
    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

Did I answer your question? Mark my post as a solution!

My Blog
Connect on Twitter
Connect on LinkedIn

View solution in original post

2 REPLIES 2
jeanL
Regular Visitor

Thanks @OwenAuger ! It works.

 

OwenAuger
Super User
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] )
)

 

ADDCOLUMNS/SUMMARIZE version

summary_table =
ADDCOLUMNS (
    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

Did I answer your question? Mark my post as a solution!

My Blog
Connect on Twitter
Connect on LinkedIn

View solution in original post

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Top Solution Authors
Top Kudoed Authors