Forum Discussion
JohanKraft
2 years agoFrequent Visitor
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_...
- Anonymous2 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
Anonymous
2 years agoNot applicable
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
2 years agoFrequent Visitor
Thank you VERY VERY much!!! This worked perfectly.