Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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.

Reply
BrianL_
New Member

DAX not working with Measure but does work with regular numbers

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.

2 REPLIES 2
BrianL_
New Member

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
)

 

v-yifanw-msft
Community Support
Community Support

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.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.