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 power Bi : 

Status = IF('DATA'[Progress] = 0 , "DRAFT", IF('DATA'[Progress] < 100 && 'DATA'[Progress] > 0 , "IN PROGRESS" , IF('DATA'[Progress] = 100 , "CLOSED")))
But It doesn't give me the right result, for a 100% of progress, the status still in progress or it should be closed.
Thanks,

 

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

     

3 Replies

  • 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

     

     

     

  • v-lili6-msft's avatar
    v-lili6-msft
    Icon for Community Support rankCommunity Support
    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

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Thank you all

    It worked