Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Anonymous
Not applicable

Profit measure isn't working, can't find solution

Apologies for brief subject. I have a Profit and Loss report, and when I've only just noticed that the Profit measure that I'm using, which is simply just this:

[Invoices Paid] (sum of all invoices (Income)) - [PO Billed] (sum of all Purchase Orders (costs)) - [Hours Billed] (sum of all Hours Billed (costs)) 

That looked simple enough and it worked, until I filtered by the month and found a problem, here is the image below: 

 

KyleAdam_0-1638182928420.png

 

If there is no invoice, then it is blank, rather than 0, so neither of the costs will be taken away. It does make sense. However, I can't think of a solution as to rectify this problem that I have. If anyone has any suggestions it will be most appreciated. 

NOTE: The three measures in profit [Invoices Paid], [PO Billed], [Hours Billed] are just sum of [insert column] measures and nothing more. They are simply just the total of a column and that's it, so for example [Invoices Paid] is this: 

Invoices Paid = SUM('Invoices (Books)'[Invoice Total]




 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@Anonymous , Based on what I got; try like

 

coalesce([Invoices Paid],0) - coalesce([PO Billed],0) - coalesce([Hours Billed],0)

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@Anonymous , Based on what I got; try like

 

coalesce([Invoices Paid],0) - coalesce([PO Billed],0) - coalesce([Hours Billed],0)

Anonymous
Not applicable

Thank you @amitchandak that worked a treat, I've not even heard of the coalesce, very useful. 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.