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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

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

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!

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!

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,

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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