Measure to add two values from two different columns that meet a condition.

[telesforo1969]

I have a table containing the rows shown in image 1. I would like help creating a measure to sum values from two columns that meet the condition of being equal in type. Figure 2 shows the result.

[Ashish_Mathur]

Hi,

These Power Query steps work

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Index"}, "Attribute", "Value"),
    #"Split Column by Character Transition" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Attribute.1", "Attribute.2"}),
    #"Pivoted Column" = Table.Pivot(#"Split Column by Character Transition", List.Distinct(#"Split Column by Character Transition"[Attribute.1]), "Attribute.1", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Index", "Attribute.2"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Tipo"}, {{"Count", each List.Sum([Costo]), type number}})
in
    #"Grouped Rows"

Hope this helps.

[Irwan · Accepted solution]

hello @telesforo1969 

in addition of @ryan_mayu 's solution, you can use DAX.

Sum =
var _Tipo = SELECTEDVALUE('Table'[Tipo1])
Return
SUM('Table'[Costo1])
+
CALCULATE(
    SUM('Table'[Costo2]),
    FILTER(
        ALL('Table'),
        'Table'[Tipo2]=_Tipo
    )
)

you can choose which way that suit your preferences.

Hope this will help.

Thank you.

[telesforo1969]

Thank you very much, I implemented the measure in my data, it worked correctly; I just had to replace the function: ALL with ALLSELECTED

[ryan_mayu]

@telesforo1969 

you can try this in PQ

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUXICYkMDMGGgFKsTDRYASRiBBE2hgs5QQWOQoBlIMBYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Tipo1 = _t, Tipo2 = _t, #"Costo 1" = _t, #"Costo 2" = _t]),
Custom1 = Table.SelectColumns(Source, {"Tipo1", "Costo 1"}),
tb1 = Table.RenameColumns(Custom1,{{"Tipo1", "Tipo"}, {"Costo 1", "Costo"}}),
Custom2 = Table.SelectColumns(Source, {"Tipo2", "Costo 2"}),
tb2 = Table.RenameColumns(Custom2,{{"Tipo2", "Tipo"}, {"Costo 2", "Costo"}}),
Custom3 = Table.Combine({ tb1, tb2}),
#"Changed Type" = Table.TransformColumnTypes(Custom3,{{"Costo", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Tipo"}, {{"costo", each List.Sum([Costo]), type nullable text}})
in
#"Grouped Rows"

pls see the attahment below

[telesforo1969]

I'll implement your solution and let you know. Thank you very much for now.