IF OR function for calculation to remove error that returns

Question

Hi everyone,&nbsp;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 revenue and payment columns, it's working as expected. When there is a zero in either the revenue column or payment column, I am getting a NaN error.&nbsp;&nbsp;I want to essentially write a formula that says ignore the 0 in revenue, or return it as 1 so it doesn't through off collection rate, and to also return 0 anytime payment equals 0, while revenue equals anything other than 0.&nbsp; I've written so many variations an keep running into error after error as I correct Token Literal, Token Else, Token Right Paren.&nbsp;&nbsp;I'm stumped, can anyone help me? Below is where I was closest, but have a Token Else expected error. The error highlights the last e in else.&nbsp;&nbsp;if( OR([Insurance Payment Grouping.Insurance Payment]=0,[Insurance Revenue]=0)) then 0) else ([Insurance Payment Grouping.Insurance Payment]/[Insurance Revenue]

hnguy71 · Answer

Hi&nbsp;Anonymous&nbsp;,&nbsp;My apologies. I should have tested my code with some dummy values. Try this sample code instead:let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwUNJRMlCK1YlWMjIAcwwhPFMQ2xjGBnF0oTJQHbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Insurance Payment" = _t, #"Insurance Revenue" = _t]),
    ChangedDataTypes = Table.TransformColumnTypes(Source,{{"Insurance Payment", Int64.Type}, {"Insurance Revenue", Int64.Type}}),
    Divide = Table.AddColumn(ChangedDataTypes, "Divide", each 
    
    let
        evaluation = [Insurance Payment] / [Insurance Revenue],     // Adjust to your two columns
        CheckMe = 
            try
                if evaluation = null then null else 
                if Number.IsNaN(evaluation) then 0 else 
                if Number.PositiveInfinity = evaluation then 0 else 
                if Number.NegativeInfinity = evaluation then 0 else 
                evaluation
            otherwise 0

in 
    CheckMe
    )
in
    DivideWith my sample output the results looks correct:&nbsp;&nbsp;

hnguy71 · Answer

Anonymous&nbsp;,Try this instead, assuming this is a calculated column:DIVIDE([Insurance Payment Grouping.Insurance Payment], [Insurance Revenue], 0)&nbsp;

anonymous · Answer

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

hnguy71 · Answer

Anonymous&nbsp;, you'd need to give it a column name:&nbsp;NewColumn = DIVIDE([Insurance Payment Grouping.Insurance Payment], [Insurance Revenue], 0)

anonymous · Answer

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

Forum Discussion

IF OR function for calculation to remove error that returns

10 Replies

Related content

calculate with max() function

How to make a DAX calculation return BLANK rows

SUM function doing wrongly calculation

DAX WeekNum function - Return Type

calculate function