DAX not working with Measure but does work with regular numbers

[BrianL_]

Hi Everyone,

Problem:
I am calculating percent billability for a dashboard and need the number of hours to be dynamic so slicers work with this eqution.
My equation essentially gets the percent value of the number of billable hours over 40 (for a normal week).

Percent Billability = (100*Harvest[Billable Hours])/(Harvest[NumOfWorkdays]*8)

This equation does not work.

It does work, however, if I replace the Harvest[Billable Hours] with "5" or any number corresponding to the number of days selected by the slicer.

Percent Billability = (100*Harvest[Billable Hours])/(5*8)

Details:

- Billable Hours is a column from my data

- Date is a date column from my data - this is effected by a date slicer

- NumOfWorkdays is a measure calculated using the following equation

NumOfWorkdays = 
VAR MinDate = CALCULATE(MIN('Harvest'[Date]), ALLSELECTED('Harvest'))
VAR MaxDate = CALCULATE(MAX('Harvest'[Date]), ALLSELECTED('Harvest'))
RETURN NETWORKDAYS(MinDate, MaxDate)

This is intended to select the number of work days based on the start and end dates selected in the slicer. I have confirmed it works by placing NumOfWorkdays in a card to display the value.

Troubleshooting:

When selecting any Monday and Friday as the start and end dates, the NumOfWorkdays value correctly shows 5. The value of Percent Billability is not correct though! It comes out to a tiny decimal number that I have not been able to figure out. When I replace NumOfWorkdays with 5, the Percent Billability is suddenly correct.

[BrianL_]

Unfortunately that still doesn't work.

Using my both your and my formula, the Percent Billability comes out to 5.14 for an anonymous user during the same test week. It should be ~66.

When I replace the denominator of your formula with 40, the equation result is correct.... seems to be the same issue.

I even tried using CONVERT to ensure the value is a double, which it is.

Percent Billability = 
DIVIDE (
    100 * CALCULATE ( SUM ( Harvest[Billable Hours] ) ),
    Convert(Harvest[NumOfWorkdays], DOUBLE) * 8
)

[Anonymous]

Hi @BrianL_ ,

Based on the information you provided, the issue seems to be related to how DAX handles the calculation of metrics versus static values. This may result in different behaviour when performing calculations across different rows or under different filter contexts in the slicer application. You can refer to the following steps to troubleshoot the issue:

1. Ensure that the data types all return numbers and that no implicit data type conversions have occurred that could affect the calculation.

2. Context conversion using CALCULATE can solve the problem of measure behaving abnormally in some calculations.

Percent Billability =
DIVIDE (
    100 * CALCULATE ( SUM ( Harvest[Billable Hours] ) ),
    Harvest[NumOfWorkdays] * 8
)

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details with your desired out put and pbix file without privacy information.

Best Regards,

Ada Wang

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