How to sum values in a column if they have the same ID in another column

[Anonymous]

Hi 

I want to add the weight column if they have same PO number.

For Eg.

The same PO have the weight categorized at item level. Below is a sample of data.

I want the total weight for each PO in a separate column So that later I can filter by removing duplicates.(I need the remove duplicates step because there are other columns in the data)

 Initial Data

 Required View

[drewlewis15 · Accepted solution]

If you are looking to do this with M via the Query Editor, then you would just use Group By in the Transform tab and group by the PO#, adding one Sum column to get the total sum of weight and one "All Rows" column to bring back in your weight column.

You could then expand your AllRows column to bring back in the columns that you need.

Your final product would look like this:

[drewlewis15 · Accepted solution]

If you are looking to do this with M via the Query Editor, then you would just use Group By in the Transform tab and group by the PO#, adding one Sum column to get the total sum of weight and one "All Rows" column to bring back in your weight column.

You could then expand your AllRows column to bring back in the columns that you need.

Your final product would look like this:

[Anonymous]

Thanks a lot @drewlewis15

[Anonymous]

@drewlewis15 When I do this this removes the other columns in data. I have other column in the data as well.

[Zubair_Muhammad]

@Anonymous

May be you could use a DAX Calculated Column

Wt =
CALCULATE ( SUM ( Table1[Weight] ), ALLEXCEPT ( Table1, Table1[PO#] ) )

[Anonymous]

@Zubair_Muhammad Correct me If I'm worng

We cannot see records if we have a DAX measure in our visual right?

[drewlewis15 · Accepted solution]

You should be able to bring back all other columns when you expand the new AllRows column right after the Group Rows step.

This screenshot should help... Just click the double arrows on the AllRows column, and then select all other columns that you want to bring back in.