Skip to main content
cancel
Showing results for 
Search instead 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

Reply
PBI_newuser
Post Prodigy
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.

 

PBI_newuser_0-1633418706213.png

 

1 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:

VahidDM_0-1633489146936.png

 

 

 

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

Appreciate your Kudos!!

 

View solution in original post

10 REPLIES 10
Tahreem24
Super User
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

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

PBI_newuser_1-1633436897236.png

 

VahidDM
Super User
Super User

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

 

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

PBI_newuser_0-1633420455828.png

 

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

 

Hi @VahidDM , it doesn't work. 

PBI_newuser_0-1633436799378.png

 

@PBI_newuser 

 

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

 

Appreciate your Kudos!!

 

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:

VahidDM_0-1633489146936.png

 

 

 

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

Appreciate your Kudos!!

 

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors