Aggregating multiple columns based on category filter

[vidyasagar159]

Hello All,

Aggressively learning Power BI and doing a project at the same time. But I am stuck on a logic. Please help. 

I want to Subtract two columns based on the Product category. I want to achieve the below result set. 

Here is the logic I am looking for 
IF Category = Red then (Amount_One) - (Amount_Two) else if Category = Black then (Amount_One) - (Amount_Three)

Product	Category	Amount_One	Amount_Two	Amount_Three	IF Category = Red then (Amount_One) - (Amount_Two) else if Category = Black then (Amount_One) - (Amount_Three)
A	Red	100	10	90	90
A	Red	200	20	80	180
A	Red	300	30	70	270
A	Red	400	40	60	360
A	Red	500	50	50	450
A	Red	600	60	40	540
B	Black	700	70	30	670
B	Black	800	80	20	780
B	Black	900	90	10	890
B	Black	1000	100	0	1000
B	Black	1100	110	-10	1110
B	Black	1200	120	-20	1220

Thanks,

Vidya

[V-pazhen-msft · Accepted solution]

Hi, @vidyasagar159 

IF(Table1[Category] = “Red”, [Amount_One]-[Amount_Two], [Amount_One]-[Amount_Three]]

Best, 
Paul

[vidyasagar159]

@V-pazhen-msft 

Thanks. But I am getting the following error.

A single value for column 'Category' in table 'Table1' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

Thanks,

-Vidya

[vidyasagar159]

@V-pazhen-msft 

Never Mind. It is working. I used the formula in a measure instead of a calculated column. Thank you for your help.

-Vidya