Summarize table based on sum of a column

[leodavinci]

Hello, 

in Power BI desktop I'm trying to summarize the table below.

The result I want is a table with only one ID and the related Brand with the highest quantity (obtained by summing up all the quantity for the same Brand on each ID).

I tried to create a calculated table with the formula below, but rhe result is not correct.

Table2 =

SUMMARIZE(Table1,

Table1[ID],

Table1[Brand],

"TotalQuantity",

SUMX(Table1, Table1[Quantity])

)

I'd be greatful if someone could suggest a better formula or different method.

[v-weiyan1-msft · Accepted solution]

Hi @leodavinci ,

Based on your description,
Please try the following steps:
My Sample:

1. Please try code as below to Create a Calculated table.

GroupTable = GROUPBY (
    'Table',
    'Table'[ID],
    'Table'[Brand],
    "@Quantity", SUMX(CURRENTGROUP(), 'Table'[Quantity])
)

2. Use the following code to create two Measures.

Max_Quantity = MAXX(
                    FILTER(ALL(GroupTable),'GroupTable'[Table_ID] = MAX('GroupTable'[Table_ID])),
                    'GroupTable'[@Quantity]
                )

Max_Quantity_Corresponding Brand = MAXX(FILTER(ALL(GroupTable),
                                        'GroupTable'[@Quantity] = [Max_Quantity] && 'GroupTable'[Table_ID] = MAX('GroupTable'[Table_ID])) 
                                        ,'GroupTable'[Table_Brand])

Result is as below.

Best Regards,
Yulia Yan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ray_aramburo]

Do you need the table explicitly on your dataset? If so, just go to Power Query > Group By > Select ID, Brand columns and group by sum of quantity.

If not, just use a table visual and add the fields ID, Brand and Quantity. Power BI will automatically add up the quantity if it's set as a numerical column.