cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
CHORNE21
Regular Visitor

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

10 REPLIES 10
Ashish_Mathur
Super User
Super User

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. 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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?

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
hnguy71
Solution Sage
Solution Sage

@CHORNE21 ,

Try this instead, assuming this is a calculated column:

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

 



Did I answer your question?
Please help by clicking the thumbs up button and mark my post as a solution!

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.

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

 

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


Did I answer your question?
Please help by clicking the thumbs up button and mark my post as a solution!

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

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

 

hnguy71_0-1651335784986.png

 



Did I answer your question?
Please help by clicking the thumbs up button and mark my post as a solution!

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

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:

hnguy71_1-1651346410099.png

 

 



Did I answer your question?
Please help by clicking the thumbs up button and mark my post as a solution!

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors
Top Kudoed Authors