topic Percentage - how to default a blank to 0%, rather than 100%? in DAX Commands and Tips

Percentage - how to default a blank to 0%, rather than 100%?

Jumper — Thu, 05 Sep 2024 23:34:54 GMT

Hi.

I'm trying to clean up a sheet I have inherited from my predecessor.
It involves a very basic Dax formula that the company uses to calculate staff productivity:

Productivity =
DIVIDE(
| SUM('Employee_Hours'[Job_Hours]),
| SUM('Employee_Hours'[Hours_Rostered)
) + 1

The problem with this formula is that if an employee has registered zero hours, then their productivcity defaults to 100%. 0% would be the preferred default, if that is possible.

My dax skills are pretty basic that a solution immediately jumps out at me here - is there a better way to write this formula?

Re: Percentage - how to default a blank to 0%, rather than 100%?

Jumper — Thu, 05 Sep 2024 23:46:23 GMT

Here's a screenshot if that helps explain a little better:

Re: Percentage - how to default a blank to 0%, rather than 100%?

Anonymous — Fri, 06 Sep 2024 08:38:36 GMT

Hi Jumper ,

Try to modify your formula like below:

Productivity = IF( SUM('Employee_Hours'[Hours_Rostered]) = 0, 0, DIVIDE( SUM('Employee_Hours'[Job_Hours]), SUM('Employee_Hours'[Hours_Rostered]) ) )

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Percentage - how to default a blank to 0%, rather than 100%?

Jumper — Fri, 13 Sep 2024 01:58:14 GMT

Brilliant - this works perfectly.

Thank you so much, and sorry for a slow reply (had a few days off!).