IF OR function for calculation to remove error that returns

[CHORNE21]

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 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. 

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.  I've written so many variations an keep running into error after error as I correct Token Literal, Token Else, Token Right Paren. 

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.

if( OR([Insurance Payment Grouping.Insurance Payment]=0,[Insurance Revenue]=0)) then 0) else ([Insurance Payment Grouping.Insurance Payment]/[Insurance Revenue]

[Ashish_Mathur]

Hi,

Try this measure

=coalesce(divide([Insurance Payment Grouping.Insurance Payment],[Insurance Revenue]),0)

I assume that [Insurance Payment Grouping.Insurance Payment] and [Insurance Revenue] are measures. 

[CHORNE21]

Hi Ashish, this is a PowerBI Custom Column calculation. It's giving me the Expression.Error when I try to execute this formula. Any thoughts on how to fix or update for PowerBI?

[Ashish_Mathur]

Hi,

From the error, i guess it is a Query Editor error.  In the Query Editor, try this

=try [Insurance Payment Grouping.Insurance Payment]/[Insurance Revenue] else null

If this does not work, then share the link from where i can download your PBI file.

[hnguy71]

@CHORNE21 ,

Try this instead, assuming this is a calculated column:

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

[CHORNE21]

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]

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

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

[CHORNE21]

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

[hnguy71]

Hi @CHORNE21 ,

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

[CHORNE21]

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]

Hi @CHORNE21 ,

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: