How to fix the value of a measure

[PBI_newuser]

Hi, I would like to calculate the available hours of a technician. The total available hours for a technician in a year is 1056. I want to divide the total available hours by the number of months user selected. E.g When the user select 2 months from the slicer, the number of month = 2. But the month in the table shows 0 instead of 2.

Here is the sample file.

[Tahreem24]

@PBI_newuser , For counting a no. of month as per slicer selection, so you need to create a New Measure like below:

No. of Month = DISTINCTCOUNT( TableName[MonthColumn])

So, whatever user selected from slicer it will just count the value. Then you further use this measure in your expression.

[PBI_newuser]

Hi @Tahreem24 , it doesn't work for the measure.

[VahidDM]

Hi @PBI_newuser 

Try this:

Total Hours =
VAR _NoMonth =
    COUNTA ( Append1[Month] )
VAR _TH = 1056
RETURN
    IF ( Append1[Role] = "Job", Append1[Hours Spend], _TH / _NoMonth )

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

Appreciate your Kudos!!

[PBI_newuser]

Hi @VahidDM , I got this error. "Role" field is a column.

[VahidDM]

@PBI_newuser 

Try this:

Total Hours =
VAR _NoMonth =
    COUNTA ( Append1[Month] )
VAR _TH = 1056
RETURN
    IF ( max(Append1[Role]) = "Job", Append1[Hours Spend], _TH / _NoMonth )

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

Appreciate your Kudos!!

[PBI_newuser]

Hi @VahidDM , it doesn't work. 

[VahidDM]

@PBI_newuser 

Can you share a sample of your data in a table format? after removing all sensetive data.

Appreciate your Kudos!!

[PBI_newuser]

Hi @VahidDM , here is the sample data. 

https://drive.google.com/file/d/1Nj1F5MP_E4zpPbnAvyXbHXQG4e3et9_P/view?usp=sharing

[VahidDM · Accepted solution]

Hi @PBI_newuser 

Try this:

Total Hours = 
VAR _NoMonth =
    CALCULATE (
        DISTINCTCOUNT ( Append1[Month] ),
        REMOVEFILTERS ( Append1[State], Append1[City], Append1[Role] )
    ) - 1
VAR _TH = 1056
RETURN
    IF (
        MAX ( Append1[Role] ) = "Job",
        SUM ( Append1[Hours Spent] ),
        _TH / _NoMonth
    )

  

Output:

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

Appreciate your Kudos!!

[PBI_newuser]

Hi @VahidDM , it works!! Thank you so much for your help! 😉