Calculate value of a row based on condition in another column

Question

Hello,

I am looking for a way to automatically calculate the highlighted numbers in the example dataset below. The WTD for a particular year is equal to the number in the same Value column when WTD Condition is "x". The MTD for a particular year is the sum of number in Value when MTD Condition is "x". Is there a way to calculate this using the multi-row field tool? Thank you in advance!

YearWeekValueWTD ConditionMTD Condition2023week 174 x2023week 292xx2023week 399  2023week 450  2023WTD92  2023MTD166  2024week 16 x2024week 229xx2024week 385  2024week 449  2024WTD29  2024MTD35  2025week 180 x2025week 287xx2025week 35  2025week 427  2025WTD87  2025MTD167

Qiu · Answer

@tsong57 
Maybe we can try a bit more dynamic way as below.

I alway like the tool "Transpose" and "Cross Tab", which are powerful for multi-column data processing.

0424-tsong57.yxmd

aatalai · Answer

@tsong57 try this workflow attached.

Let me know how you get on

Calculate value of a row based on condition in another column.yxmd