Forum Discussion

sdhn's avatar
sdhn
Responsive Resident
4 years ago
Solved

Calculated Column

Hi all,   I need to create calculated column in power bi as below: Column =(col1-col2)/col2   Will appreciate your help. 
Help
  • VahidDM's avatar
    VahidDM
    4 years ago

    Hi sdhn 

     

    try this:

     

    Column =
IF (
    [col2] = 0,
    BLANK (),
    IF ( [col1] - [col2] < 0, 0, ( [col1] - [col2] ) / [col2] )
)

     

    If my posts helps, please consider accepting them as solutions to help the other members find it more quickly.
    Appreciate your Kudos!!
    LinkedIn:     www.linkedin.com/in/vahid-dm/

     

     

VahidDM's avatar
VahidDM
Super User
4 years ago

Hi sdhn 

 

Can you share a sample of your data in a text format?

 

BTW, try this:

Column =([col1]-[col2])/[col2]

 

 

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

 

 

sdhn's avatar
sdhn
Responsive Resident
4 years ago

Sum values come in new  column come as "NaN" & in one row value of New Column is  "Infinity".  Rest rows are fine.   Any advise Thanks 

  • VahidDM's avatar
    VahidDM
    Super User
    4 years ago

    sdhn 

    Try this:

     

    Column =IF([col2]=0,blank(),([col1]-[col2])/[col2])

     

    If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
    Appreciate your Kudos!!
    LinkedIn:     www.linkedin.com/in/vahid-dm/

     

     

    • sdhn's avatar
      sdhn
      Responsive Resident
      4 years ago

      Great , thi ssolve the issue.  One more request.

      I am getting negative figures in few rows. 

      as -0.3 %.  I want to display any "-" as '0'.   Can you help on this too? thanks 

       

      • sdhn's avatar
        sdhn
        Responsive Resident
        4 years ago

        I meant Just display 0 if there is any negative "-"  number