Calculated Column - SUM Based on Criteria from Multiple Columns in Table

[TheBarron]

Hi all

Been struggling on this one, and got a lot of the way there with CALCULATE, SUM, SUMX, FILTER etc however need to turn to the experts now.

Costing a set of items as they roll through production process across different material types. W -> GB -> DH -> BB -> FP. Need associated costs from each stage to roll up under relevant types.

Aiming to recreate the M column in PBI - formula within current cell is:

=IF(F2="RM: M",(E2*D2)/B2,IF(OR(LEFT(F2,2)="FP",LEFT(F2,3)="WIP"),SUMIF(A:A,C2,G:G)*(D2/B2),0))

Basically if Group = RM: M - do calulcation - this is fine, the bit i am having trouble with is the second part:

IF(OR(LEFT(F2,2)="FP",LEFT(F2,3)="WIP"),SUMIF(A:A,C2,G:G)*(D2/B2),0)

If Group begins either FP or WIP sumif column G (labelled M) based on item in column C from criteria in A then perform said calculation.

I can seem to get it for one level up as in i can work from GB and will sum for W but for DH and beyond it returns 0 - any help appreciated

[Anonymous · Accepted solution]

Hi @TheBarron ,

If I understand correctly, the issue is that you want to sum from multiple columns. Please try the following methods and check if they can solve your problem:

1.Create a calculated column.

RM_M_Cost = IF([Group] = "RM: M", ([E] * [D]) / [B], BLANK())

2.Add a calculated column for FP condition.

FP_WIP_Cost =
IF( OR(LEFT([F], 2) = "FP", LEFT([F], 3) = "WIP"),
CALCULATE( SUM([G]),
FILTER( ALL('Table'), 'Table'[A] = EARLIER('Table'[C]) ) ) * ([D] / [B]), 0 )

3.You can create a final calculated column that combines both conditions.

Final_Cost =
IF(
    NOT(ISBLANK([RM_M_Cost])), [RM_M_Cost], [FP_WIP_Cost] )

If the above ones can’t help you get it working, could you please provide more raw data(exclude sensitive data) with Text format or screenshot to make a deep troubleshooting? It would be helpful to find out the solution.

Best Regards