Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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,
Solved! Go to Solution.
Hi @eburke
You should be able to use the IF function for this.
= IF ([Customer Assigned] - [PrePaid] < 0 , 0 , [Customer Assigned] - [PrePaid] )
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) )
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
You should be able to use the IF function for this.
= IF ([Customer Assigned] - [PrePaid] < 0 , 0 , [Customer Assigned] - [PrePaid] )
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) )
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 🙂
Bwahahaaa... excuse me while I hang my head in embarrassment. Thanks you that worked brilliantly.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |