Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

IF OR function for calculation to remove error that returns

Hi everyone,   I'm attempting to write an if or formula that will calculate the percentage of payment of revenue. Essentially a collection rate. For all that have numbers greater than 0 in both rev...
Need Help
hnguy71's avatar
hnguy71
Super User
4 years ago

Anonymous ,

Try this instead, assuming this is a calculated column:

DIVIDE([Insurance Payment Grouping.Insurance Payment], [Insurance Revenue], 0)

 

Anonymous's avatar
Anonymous
Not applicable
4 years ago

The syntax works, but I get the following error:   Expression.Error: The name 'DIVIDE' wasn't recognized. Make sure it's spelled correctly. Also tried it in lower case, same expression error.

  • hnguy71's avatar
    hnguy71
    Super User
    4 years ago

    Anonymous , you'd need to give it a column name:

     

    NewColumn = DIVIDE([Insurance Payment Grouping.Insurance Payment], [Insurance Revenue], 0)
    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      The new column name was already accounted for: Insurance Collection %. Still the error.

      • hnguy71's avatar
        hnguy71
        Super User
        4 years ago

        Hi Anonymous ,


        To confirm, are you building this within PowerQuery or as a DAX column? DAX definitely has the DIVIDE function but PowerQuery does not.

         

        Looking at your post it is starting to look like you're building this within PowerQuery. In your Custom Column, can you input the following formula:

        let 
        evaluation = [Insurance Payment Grouping.Insurance Payment] / [Insurance Payment Grouping.Insurance Payment],
        CheckMe = 
        try 
            if evaluation = null then null else 
            if Number.IsNaN(evaluation) then 0 else
            if Number.NegativeInfinity(evaluation) then 0 else
            if Number.PositiveInfinity(evaluation) then 0 else 
            evaluation 
        otherwise 0
    in
CheckMe