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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
96 | |
77 | |
66 | |
53 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |