Forum Discussion
IF statement returns 0%
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!
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
2 Replies
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