cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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
MVP

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.

MVP

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.

MVP

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.

MVP

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

Post Prodigy
MVP

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! 😉

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors