How to summation for paricular row in power bi

[SBC]

Hi,

How to do summation for a particular row in power bi 

Input data:

curve	exp	suv
LX_qwr	12	16
NS_MEX	-2	4
NS_AOC	10	-34.56
NS_XTV	-22.40	22.42
AC_X1C	-33.52	22

we need to sum only which starts with NS_ and we need to show that total under NS_MEX,remaining curve should be show as it is without any change.

Expected output:

curve	exp	suv
LX_qwr	12	16
NS_MEX	-47.92	13.86
NS_AOC	10	-34.56
NS_XTV	-22.40	22.42
AC_X1C	-33.52	22

Thanks,

SBC

[v-jingzhang]

Hi @SBC 

Sorry the expected result -47.92 and 13.86 seem not correct according to your description. For curves starting with NS_,

exp: (-2)+10+(-22.40)=-14.4

suv: 4+(-34.56)+22.42=-8.14

If above results are the correct results you want, you can try my solution

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8omILywvUtJRMjQCEWZKsTrRSn7B8b6uEUC+LkjQBCbm6O8MUmMAkjA20TOFK44ICQMrNtIzAUmCaCOwnKNzfIShM1i9sZ6pEVhOKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [curve = _t, exp = _t, suv = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"curve", type text}, {"exp", type number}, {"suv", type number}}),
    
    /* replace values in exp and suv columns */
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type", each [exp], each if [curve] = "NS_MEX" then List.Sum(Table.SelectRows(#"Changed Type", each Text.StartsWith([curve], "NS_"))[exp]) else [exp],Replacer.ReplaceValue,{"exp"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value", each [suv], each if [curve] = "NS_MEX" then List.Sum(Table.SelectRows(#"Replaced Value", each Text.StartsWith([curve], "NS_"))[suv]) else [suv],Replacer.ReplaceValue,{"suv"})
in
    #"Replaced Value1"

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[SBC]

Hi @v-jingzhang ,

Thanks for reponsding, when i try to implement that logic in my power bi report taking lots of time to load the data.

is there alternate way to achieve this ? using DAX or something🙂

Thanks,

SBC