topic Re: Ignoring Negative Values in Calculated Column in DAX Commands and Tips

Ignoring Negative Values in Calculated Column

Anonymous — Wed, 16 Feb 2022 06:48:10 GMT

Hi There,

I am trying to create a calculated meaure where i am going subtraction from two columns which contains values as below :

I need the result value which is showing in green(2) however when i am doing subtraction from (Ask-Actual) i am getting a result (-1) however i want to ignore negative values in my measure so that my result value will appear as (2).

I have created 3 measures which are as below :

Total_Ask=Sum(ask)

Total Actual-Sum(Actual)
Gap=Total Ask-Total Actual

Can anyone please help me in figuring out how can i calculate Gap while ignoring Negative values?

Regards,

Ashish

Re: Ignoring Negative Values in Calculated Column

amitchandak — Wed, 16 Feb 2022 10:32:28 GMT

Anonymous , of those, are column

sumx(table, if([Total Ask]-[Total Actual]>0 ,[Total Ask]-[Total Actual], blank()) )

Re: Ignoring Negative Values in Calculated Column

Anonymous — Mon, 21 Feb 2022 09:20:42 GMT

Hi Anonymous ,

Please check the formula.

measure1 = SUM('Table'[ask])-SUM('Table'[actual]) Measure2 = IF([measure1]<0,0,[measure1]) Measure3 = IF(ISINSCOPE('Table'[ask]),[measure1],SUMX(ALL('Table'),[Measure2]))

Best Regards,

Jay