Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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]
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.
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.
Try this instead, assuming this is a calculated column:
DIVIDE([Insurance Payment Grouping.Insurance Payment], [Insurance Revenue], 0)
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)
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
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:
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
92 | |
87 | |
84 | |
65 | |
49 |
User | Count |
---|---|
140 | |
114 | |
110 | |
59 | |
59 |