topic Re: Sum and Difference based on column field in Developer

Sum and Difference based on column field

jenani_user — Thu, 07 Nov 2024 11:58:55 GMT

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

Re: Sum and Difference based on column field

KNP — Fri, 08 Nov 2024 00:53:30 GMT

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

Re: Sum and Difference based on column field

Anonymous — Fri, 08 Nov 2024 06:26:33 GMT

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