Sum and Difference based on column field

[jenani_user]

Hi All,

I have table Similar to the Input image shown , how do I obtain the output similar to the image shown,

INPUT:

OUTPUT:

Kindly suggest any idea as there are around 10 columns similar to values(in B column of input)

Thanks

Jen

[v-yangliu-msft · Accepted solution]

Thanks for the reply from KNP , please allow me to provide another insight: 
Hi  @jenani_user ,

Here are the steps you can follow：

1. Create calculated table.

Table =
var _table1=
SUMMARIZE(
    'Input',
    [A],
    "C","B+C",
    "B",
    SUMX(FILTER('Input',[A]=EARLIER([A])&&[C]="B"),[B])+
    SUMX(FILTER('Input',[A]=EARLIER([A])&&[C]="C"),[B]))
var _table2=
SUMMARIZE(
    _table1,
    [A],
    "C","A-(B+C)",
    "B",
    SUMX(FILTER('Input',[A]=EARLIER([A])&&[C]="A"),[B])-
    SUMX(FILTER(_table1,[A]=EARLIER([A])&&[C]="B+C"),[B]))
var _table=
SUMMARIZE(
    'Input',[A],[C],[B])
return
UNION(
    _table,_table1,_table2)

2. Result:

Best Regards,

Liu Yang

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

[KNP]

I'm not sure how complex your actual scenario is but have a look at the attached and see if it helps at all.