cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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: 

klchan06_1-1645191106029.png

 


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:

 

klchan06_2-1645191159062.png


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!

 

2 ACCEPTED SOLUTIONS
Russell-PBI
Resolver II
Resolver II

Hi @Anonymous, have you tried just using DIVIDE?:

ProductivityA = DIVIDE([InvoicingTargetInHours], [NormWorkingHours])

 

View solution in original post

tackytechtom
Super User
Super User

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.tackytech.blog/

https://www.instagram.com/tackytechtom/





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

2 REPLIES 2
tackytechtom
Super User
Super User

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.tackytech.blog/

https://www.instagram.com/tackytechtom/





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Russell-PBI
Resolver II
Resolver II

Hi @Anonymous, have you tried just using DIVIDE?:

ProductivityA = DIVIDE([InvoicingTargetInHours], [NormWorkingHours])

 

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors