Forum Discussion

TonyBarron's avatar
TonyBarron
Frequent Visitor
2 years ago
Solved

DAX SUMIF Equivalent within Calculated Column to create recurring results

Hi

 

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

 

Here is my excel output and what i am trying to recreate in DAX:

CodeBOMQtyCompCodeQuantityDomesticPriceComponentGroupM
C123301PCN10012.060.0155PK: CE0
C123301BB1010.040339.4918WIP: BB0.47972434
C123301OHPCG0.03968.228OHead0
C123301PCN101-330-212.060.0775PK: C0
C123301PBX101-C12330-31.01080.1941PK: B0
BB101376OHBBS34007.609OHead0
BB101376DH10140029.5133WIP: DH11.90382979
DH101400RH0077315.74238272RM: H0
DH101400RS0812000.385RM: MI0
DH101400OHDHS34000.147OHead0
DH101400GB10140019.4502WIP: GB11.1896
DH101400RH00510914.3639368RM: H0
DH101400RH0063616.16145414RM: H0
DH101400RH0013612.09092375RM: H0
DH101400RH0041813.01789457RM: H0
GB101400W10140019.4502WIP: W11.1896
W101400OHWS34005.52OHead0
W101400RH128272.54554248RM: H0
W101400RH0052714.3639368RM: H0
W101400RH006716.16145414RM: H0
W101400RH009513.16298565RM: H0
W101400RH0072315.74238272RM: H0
W101400RM00372000.5847RM: M10.5246
W101400RL0020.721.68RM: MI0
W101400RL009250.495RM: MI0
W101400RL001120.514RM: MI0
W101400CH0811.218.59RM: MI0
W101400RL0060.0714.6RM: MI0
W101400RM0273500.76RM: M0.665

 

 

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

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi TonyBarron ,

    I have created a simple sample, please refer to my pbix file to see if it helps you.

    Create a column.

    Column =
VAR _edb = ( 'Table'[DomesticPrice] * 'Table'[Quantity] ) / 'Table'[BOMQty]
VAR _leftf =
    LEFT ( 'Table'[ComponentGroup], 2 )
VAR _leftf3 =
    LEFT ( 'Table'[ComponentGroup], 3 )
VAR _sumif =
    CALCULATE (
        SUM ( 'Table'[M] ),
        FILTER ( 'Table', 'Table'[CompCode] IN VALUES ( 'Table'[Code] ) )
    )
RETURN
    IF (
        'Table'[ComponentGroup] = "RM: M",
        _edb,
        IF ( _leftf = " FP" || _leftf3 = "WIP", _sumif, 0 )
    )

     

     

     

    How to Get Your Question Answered Quickly 

     

    If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

     

    Best Regards
    Community Support Team _ Rongtie

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

     

     

     

     

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    2 years ago

    TonyBarron , Try if this can help

     

    M Column =
    VAR _CompCode = [CompCode]
    VAR _Group = [ComponentGroup]
    VAR _BOMQty = [BOMQty]
    VAR _DomesticPrice = [DomesticPrice]
    VAR _Quantity = [Quantity]
    VAR Code = [Code]

    VAR RM_M_Calculation = IF(LEFT(_Group, 6) = "RM: M", (_DomesticPrice * _Quantity) / _BOMQty, BLANK())

    VAR WIP_FP_Calculation =
    IF(
    OR(LEFT(_Group, 2) = "FP", LEFT(_Group, 3) = "WIP"),
    CALCULATE(
    SUM('Table'[M Column]),
    FILTER(
    'Table',
    'Table'[Code] = _CompCode
    )
    ) * (_Quantity / _BOMQty),
    BLANK()
    )

    RETURN IF(RM_M_Calculation <> BLANK(), RM_M_Calculation, WIP_FP_Calculation)

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi TonyBarron ,

    I have created a simple sample, please refer to my pbix file to see if it helps you.

    Create a column.

    Column =
VAR _edb = ( 'Table'[DomesticPrice] * 'Table'[Quantity] ) / 'Table'[BOMQty]
VAR _leftf =
    LEFT ( 'Table'[ComponentGroup], 2 )
VAR _leftf3 =
    LEFT ( 'Table'[ComponentGroup], 3 )
VAR _sumif =
    CALCULATE (
        SUM ( 'Table'[M] ),
        FILTER ( 'Table', 'Table'[CompCode] IN VALUES ( 'Table'[Code] ) )
    )
RETURN
    IF (
        'Table'[ComponentGroup] = "RM: M",
        _edb,
        IF ( _leftf = " FP" || _leftf3 = "WIP", _sumif, 0 )
    )

     

     

     

    How to Get Your Question Answered Quickly 

     

    If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

     

    Best Regards
    Community Support Team _ Rongtie

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