Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Create a calculated column from a percent column

Hi all,  I need a formula please to create a column from another column, Here'is my excel formula : =IFS(Q2=0%,"DRAFT",(AND(Q2>0%,Q2<100%)),"IN PROGRESS",Q2=100%,"CLOSED") I tried this formula on ...
Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    6 years ago

    HI  Anonymous 

    Just adjust the the formula as below:

    Status =
IF (
    'DATA'[Progress] = 0,
    "DRAFT",
    IF (
        'DATA'[Progress] < 1
            && 'DATA'[Progress] > 0,
        "IN PROGRESS",
        IF ( 'DATA'[Progress] = 1, "CLOSED" )
    )
)

     

     

    Regards,

    Lin

     

TomMartens's avatar
TomMartens
Icon for Super User rankSuper User
6 years ago

Hey Anonymous ,

 

I use this DAX statement to create a calculated column:

Status = 
var __Progress = 'Table'[Progress]
return
SWITCH(
    TRUE() 
    , __Progress = 0 , "Draft"
    , __Progress = 1 , "Closed"
    , "In Progress"
)

As you can see, it's using a variable to avoid multiple evaluations of the Progress column. The table will look like this:

The DAX is not that different from the code you provided, you might double-check the value of the Progress column, it 75% is expressed as 0.75, this would explain why your statement returns "IN PROGRESS" instead of "CLOSED".

 

Regards,

Tom