Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Create a Calculated Column Returning 0 or 1 Depending Whether a Condition is Satisfied

Hi guys I need your help! I'm trying to create a calculated column which returns either 0 or 1 if a value from another column is satisfied. For example, I have a month column and cumulative sales column. I want the month where I first hit the target sales (which is a constant) to have a value of 1 and 0 otherwise. How can I create that column?

 

Thank you!

Need Help
  • Floriankx's avatar
    Floriankx
    8 years ago

    I guess there are several ways. Here is one way:

     

    =VAR SalesTD=fSales[SalesTD]
VAR TargetSalesTD=200 //Target value, can be changed
RETURN IF(SalesTD>=TargetSalesTD&&
RANKX(FILTER(fSales;fSales[SalesTD]>=TargetSalesTD);fSales[SalesTD];;ASC)=1;1;0)

1 Reply

  • Floriankx's avatar
    Floriankx
    Icon for Solution Sage rankSolution Sage
    8 years ago

    I guess there are several ways. Here is one way:

     

    =VAR SalesTD=fSales[SalesTD]
VAR TargetSalesTD=200 //Target value, can be changed
RETURN IF(SalesTD>=TargetSalesTD&&
RANKX(FILTER(fSales;fSales[SalesTD]>=TargetSalesTD);fSales[SalesTD];;ASC)=1;1;0)