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.
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.
Solved! Go to 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 , 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.
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!!
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!!
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:
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
98 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |