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
Anonymous's avatar
Anonymous
Not applicable
4 years ago

The let formula goes through, however, all values come back at 0. I want to divide the payment by the revenue to get a collection percentage. When I added the formula in pic for Divide, I got a Rightparen error

hnguy71's avatar
hnguy71
Super User
4 years ago

Hi Anonymous ,

 

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
    Divide


With my sample output the results looks correct: