Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi guys,
My previous colleague wrote an Dax If statement to return a percentage of a number. Below you can see the dax statement
The problem is that I don't understand this statement.
Dax If statement:
" ProductivityA = IF([InvoicingTargetInHours] - [NormWorkingHours] = 0, 0, IFERROR(([InvoicingTargetInHours] / [NormWorkingHours]), 0)) "
The measure is calculating the percentage correct if the InvoicingTargetInHours and the NormWorkingHours are different, see picture below:
But once the InvoicingTargetInHours and the NormWorkingHours are the same number, it return 0% when in fact it should return 100% as productivity, see picture below:
Can someone help explain this statement to me and how I should fix this dax?
Should I rewrite or not? Im clueless at this point.
Thanks guys!
Solved! Go to Solution.
Hi @Anonymous, have you tried just using DIVIDE?:
ProductivityA = DIVIDE([InvoicingTargetInHours], [NormWorkingHours])
Hi @Anonymous ,
The reason why it is displaying 0% is due to the first part of your if statement (bold) where it explicitly returns 0 if InvoicingTargetInHours and NormWorkingHours are equal:
ProductivityA =
IF (
[InvoicingTargetInHours] - [NormWorkingHours] = 0,
0,
IFERROR (
( [InvoicingTargetInHours] / [NormWorkingHours] ),
0)
)
If you would like to diplay 100%, I'd suggest to use the DIVIDE function as Russel-PBI suggested:
ProductivityA = DIVIDE([InvoicingTargetInHours], [NormWorkingHours])
/Tom
https://www.instagram.com/tackytechtom/
Did I answer your question❓➡️ Please, mark my post as a solution ✔️ |
Also happily accepting Kudos 🙂 |
Feel free to connect with me on LinkedIn! |
#proudtobeasuperuser |
Hi @Anonymous ,
The reason why it is displaying 0% is due to the first part of your if statement (bold) where it explicitly returns 0 if InvoicingTargetInHours and NormWorkingHours are equal:
ProductivityA =
IF (
[InvoicingTargetInHours] - [NormWorkingHours] = 0,
0,
IFERROR (
( [InvoicingTargetInHours] / [NormWorkingHours] ),
0)
)
If you would like to diplay 100%, I'd suggest to use the DIVIDE function as Russel-PBI suggested:
ProductivityA = DIVIDE([InvoicingTargetInHours], [NormWorkingHours])
/Tom
https://www.instagram.com/tackytechtom/
Did I answer your question❓➡️ Please, mark my post as a solution ✔️ |
Also happily accepting Kudos 🙂 |
Feel free to connect with me on LinkedIn! |
#proudtobeasuperuser |
Hi @Anonymous, have you tried just using DIVIDE?:
ProductivityA = DIVIDE([InvoicingTargetInHours], [NormWorkingHours])