Forum Discussion
Sum and Difference based on column field
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
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
2 Replies
I'm not sure how complex your actual scenario is but have a look at the attached and see if it helps at all.
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
