Forum Discussion

eburke's avatar
eburke
Helper II
8 years ago
Solved

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 Prep...
Need Help
  • Phil_Seamark's avatar
    Phil_Seamark
    8 years ago

    Hi eburke

     

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

     

     

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

     

  • Phil_Seamark's avatar
    Phil_Seamark
    8 years ago

    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)
			)
  • eburke's avatar
    eburke
    8 years ago

    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,

Phil_Seamark's avatar
Phil_Seamark
Microsoft Employee
8 years ago

Hi eburke

 

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

 

 

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

 

eburke's avatar
eburke
Helper II
8 years ago

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

  • Phil_Seamark's avatar
    Phil_Seamark
    Microsoft Employee
    8 years ago

    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)
			)
    • eburke's avatar
      eburke
      Helper II
      8 years ago

      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,

      • Phil_Seamark's avatar
        Phil_Seamark
        Microsoft Employee
        8 years ago

        HI eburke

         

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