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/

     

     

8 Replies

  • 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

      If both columns have "0" value output come as NaN

      Column =([0]-[0])/[0]

       

      output is "NaN"

       

      in one row I have value for column 1 = 13

      column 2 = 0

       

      Column =([13]-[0])/[0]

      Out put is  infinity