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,
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])
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 |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
58 | |
50 | |
44 | |
19 | |
18 |