Forum Discussion

JohanKraft's avatar
JohanKraft
Frequent Visitor
2 years ago
Solved

Calculate two columns when the third column contains a specific value

Hi all, Please help me! I have two problems ðŸ˜‰   Problem 1 How do I calculate two columns when there is a specific value in the third column?   When the value "FREIGHT" appears in column "Item_...
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi JohanKraft ,

    I created a sample pbix file(see the attachment), please check if that is what you want.

    Count of Freight = 
    VAR _orderno =
        SELECTEDVALUE ( 'Table'[Order no] )
    RETURN
        CALCULATE (
            COUNT ( 'Table'[Order no] ),
            FILTER (
                ALLSELECTED ( 'Table' ),
                'Table'[Order no] = _orderno
                    && IFERROR ( SEARCH ( "FREIGHT", 'Table'[Item no], 1, 0 ), 0 ) > 0
            )
        )
    Sum of amount with special value = 
    VAR _orderno =
        SELECTEDVALUE ( 'Table'[Order no] )
    RETURN
        CALCULATE (
            SUM ( 'Table'[Amount] ),
            FILTER (
                ALLSELECTED ( 'Table' ),
                'Table'[Order no] = _orderno
                    && [Count of Freight] > 0
            )
        )
    Sum of amount without special value = 
    VAR _orderno =
        SELECTEDVALUE ( 'Table'[Order no] )
    RETURN
        CALCULATE (
            SUM ( 'Table'[Amount] ),
            FILTER (
                ALLSELECTED ( 'Table' ),
                'Table'[Order no] = _orderno
                    && ISBLANK ( [Count of Freight] )
            )
        )

    Best Regards