Unable to add condition on 2 measures.

[Anonymous]

Hi Team,

I have a scenarios where I need to create 2 measures with some logic and basedon the 2 measure i need to create a new column or measure based on a condition. 

2 measures Created 

 Measure 1 : SUMX(Purch,(Purch[Inovice-Amt] * RELATED(Currency_Conv[Curr_Conv])))

Measure 2 : SUM('Purch'[Invoice_Qty])

I need to create a measure/column based on the 2 measures defined above. 

Measure 3 :  If(Measure 1 < 0 and Measure 2 > 0 ,0 , Measure 2)  

I am able to create the measure but unable to get the desired output for which my My data in table is mismatching. 

For few Id's I am getting multiple rows which it is being summarized and giving correct values to Measure 1 and Measure 2. But for Measure 3 it should show as 0 but its showing my measure 2 value as it falling in else statement. I knew something is missing in measure 3 calculation. I would need it output of measure 3 as 0 not measure 2. 

I mean how do I write in measure 3 that it should check summarized value for both Measure 1 and Measure 2 based on that it should calculate measure 3 not row by row.

Please guide me how to implement Measure 3 .

Highly appreciate for the help.

Thanks in advance.

[stevedep]

Can you share some sample data?

[Anonymous]

Hi @stevedep ,

Thanks for the response. Below is the sample data

PID	P_Desc	Purch_group	Measure 1	Measure 2	Measure 3
901157156	Abcd	21	634.56	32	32
911147167	nesad	34	54.7	4	4
813143561	qwe	20	-10758	329	329

But I need to get as below  where i am unable to add condition in Measure 3 to get as below output. For measure 3 third row I need to get 0 but getting 329. 

PID	P_Desc	Purch_group	Measure 1	Measure 2	Measure 3
901157156	Abcd	21	634.56	32	32
911147167	nesad	34	54.7	4	4
813143561	qwe	20	-10758	329	0

Thanks in advance.

[amitchandak]

@Anonymous ,

Try a measure like

Sumx(values(Table[PID]), If([Measure 1] < 0 && [Measure ] > 0 ,0 , [Measure 2]))

or

Sumx(Summarize(Table[PID],"_1", If([Measure 1] < 0 && [Measure ] > 0 ,0 , [Measure 2])),[_1])