cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Post Prodigy

## How to fix the value of a measure

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.

1 ACCEPTED SOLUTION
Super User

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.

10 REPLIES 10
Super User

@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.

Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, Sales Analysis Report , Fortune 500 Companies Analysis , Revenue Tracking Dashboard
Post Prodigy

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

Super User

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.

Post Prodigy

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

Super User

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.

Post Prodigy

Hi @VahidDM , it doesn't work.

Super User

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

Post Prodigy
Super User

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.

Post Prodigy

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