Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Ignoring Negative Values in Calculated Column

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 

Need Assistance
Need Help
  • amitchandak's avatar
    amitchandak
    4 years ago

    Anonymous , of those, are column

     

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

  • Anonymous's avatar
    Anonymous
    4 years ago

    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

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Anonymous , of those, are column

     

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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