cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
eburke
Helper II
Helper II

ignore values less than zero

Hi guys,

 

I'm pretty sure this should be a simple one but I can't find an answer to it searching.  I have a very simple measure:

 

Customer Assigned - PrePaid Hours = Billable Hours

 

The Prepaid Hours is a set amount and the Customer Assigned hours is the number of hours they have used in a month, I want to calculate the Billable Hours i.e. number of hours they have used in a month ABOVE their set PrePaid hours.  The formula above is fine except for when they have used less than their PrePaid Hours and therefore it gives a negative figure:

 

CA 1 hour - PrePaid 1.5 Hours = Billable -.5 hours

 

What I'm after is a formula that states if the value is a negative change it to 0:

 

CA 1 hour - PrePaid 1.5 = 0

 

Thanks,

3 ACCEPTED SOLUTIONS
Phil_Seamark
Microsoft
Microsoft

Hi @eburke

 

You should be able to use the IF function for this.

 

 

= IF ([Customer Assigned] - [PrePaid] < 0 , 0 , [Customer Assigned] - [PrePaid] )

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

HI @eburke

 

Just wrap a SUMX Function around it to force the Total calc to be row by row

 

Measure 2 = 
	SUMX(
		'Table2',
		IF(
			'Table2'[Customer Assigned]-'Table2'[Pre Paid]>0,
			-- THEN --
			'Table2'[Customer Assigned]-'Table2'[Pre Paid],
			-- ELSE --
			0)
			)

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

When I try this formula the THEN and ELSE come up green like Power BI doesn't recognise it as a function.  Also will it matter that the Customer Assigned and PrePaid Meaures are on different tables?  Thanks,

View solution in original post

6 REPLIES 6
Phil_Seamark
Microsoft
Microsoft

Hi @eburke

 

You should be able to use the IF function for this.

 

 

= IF ([Customer Assigned] - [PrePaid] < 0 , 0 , [Customer Assigned] - [PrePaid] )

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

Thank you, I thought it was something like that I just had my brackets in the wrong place. 

 

My issue now is how do I apply that to the Totals at the bottom of the table, otherwise it does Total CA - Total PrePaid = Billable Hours, whereas I actually want to show the column total of Billable Hours

HI @eburke

 

Just wrap a SUMX Function around it to force the Total calc to be row by row

 

Measure 2 = 
	SUMX(
		'Table2',
		IF(
			'Table2'[Customer Assigned]-'Table2'[Pre Paid]>0,
			-- THEN --
			'Table2'[Customer Assigned]-'Table2'[Pre Paid],
			-- ELSE --
			0)
			)

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

When I try this formula the THEN and ELSE come up green like Power BI doesn't recognise it as a function.  Also will it matter that the Customer Assigned and PrePaid Meaures are on different tables?  Thanks,

View solution in original post

HI @eburke

 

These are comments and just designed to explain the logic.  The green colour highlights comments 🙂


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Bwahahaaa... excuse me while I hang my head in embarrassment.  Thanks you that worked brilliantly.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.