aggregate product by the sum of max cumulative_sales of each country

Question

I have a dataset with the variables product_id, date, country_id, cumulative_sales

I need a calculated field that returns for each product_id, the sum of the maximum cumulative_sales of each country in which the product_id is sold.

In pseudocode/python

for prod_id in df.product_id.unique():
    list.append(
        (prod_id, 
        sum[
            max(df[product_id==prod_id and country_id==country].cumulative_sales) 
            for country in df[product_id==prod_id].country_id.unique()
           ]
         ))

Answer 1

Create a calculated field:

{ FIXED [Product ID], [Country ID] : MAX([Cumulative Sales]) }

Then just SUM that calculated field. For example, drag [Product ID] into Rows, then your calculated field into Text. Set the aggregation to SUM, and you should be good to go.