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 produ...
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.

     

     

     

     

amitchandak's avatar
amitchandak
Icon for Super User rankSuper 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)