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_no" I would like to calculate the amount related to a specific order, in this case order number 10.
20+10+40=70. 

 

Problem 2

How do I do the opposite to above? If the column "Item_no" contains "FREIGHT" in an order, then do not calculate the values in that order, but calculate orders without the item no "FREIGHT". In this case order_no 11.

30+10+20=60

 

 

Thank you very much in advance!!!
//Johan Kraft

 

 

Need Help
  • 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

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    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

    • JohanKraft's avatar
      JohanKraft
      Frequent Visitor
      2 years ago

      Thank you VERY VERY much!!! This worked perfectly.