How to loop through values in a column in DAX

Question

I have a table that looks like this:

Document PartNum   Cost
A        1         5
A        1         5
A        2         3
A        2         3
B        1         1
B        1         1
B        3         4
B        3         4

I am trying to get the SUM of the cost for each part (counted once) on each document. So my new column would look like this:

Document PartNum   Cost  NewColumn
A        1         5     8
A        1         5     8 
A        2         3     8
A        2         3     8
B        1         1     5
B        1         1     5
B        3         4     5
B        3         4     5

In R I would use a for loop, max and an append function of some kind. I am struggling to find a solution using DAX in PowerBI. Thank you in advance!

Answer 1

I have a solution using MQuery & DAX.

#"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"PartNum", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type1", "DocPart", each [Document]&[PartNum]),
#"Removed Duplicates" = Table.Distinct(#"Added Custom", {"DocPart"})

Step 1: Make PartNumber type text

Step 2: Create new custom column by concatenation of Document & PartNumber.

Step 3: Remove duplicates from New Custom Column.

Step 4: Now close N apply.

Step 5: create a new Measure using dax:

TotalCost = CALCULATE(SUM(uni[Cost]),ALL(uni[DocPart],uni[PartNumber],uni[Cost]))

Step 6: Drag required columns.

Note: Please refer screenshot for output data before applying code