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 all,
I have a fairly straightforward requirement that I can't seem to crack. Here's the situation:
DimMatterInfo (Matter # is primary key)
DimTimekeeper (TKID is primary key)
DimTitle (Title is primary key)
Measures that are working:
[Total Billable Hours Worked] = SUM( 'Matter Fact'[Billable Hours Worked] )
[Billable Hours Worked Feb-June] =
CALCULATE(
[Total Billable Hours Worked],
DimPeriod[period_month_num] >= 2 && DimPeriod[period_month_num] <= 6
)
[Analyst Hours Worked] =
CALCULATE (
[Total Billable Hours Worked],
Timekeeper[Title] = "Analyst"
)
The key requirement, and what's tripping me up, is that I only want to run metrics on matters with [Total Billable Hours Worked Feb-June] > 20. I've achieved this on the visual using the visual level filter on the table shown above.
What I'm trying to write is a measure that calculates average Analyst Hours Per Job, subject to the overall limitation that the [Total Billable Hours Worked] > 20. So in the above example, the measure would be: (19.0 + 49.5 + 33.5) / 3 = 34.0. Basically, I need a formula that returns "3" for the non-blank analyst rows, subject to the [Total Billable Hours Worked Feb-June] > 20 limitation.
I'm ultimately not going to show the [Total Billable Hours Worked Feb-June] on the table -- it's just needed for a filter.
Any help would be greatly appreciated!
I've tried the following formula, but it's returning 6 rows (the correct answer should be 3). Obviously I need to filter to only show "Analyst" which comes from a related table. I guess at this point I'm not sure if I need to modify the SUMMARIZE statement or somehow filter using a RELATED function?
Analyst # of Matters =
VAR TempTable =
FILTER(
SUMMARIZE('Matter Fact',
'Matter Fact'[Matter #],
"Total Hours Worked", SUM( 'Matter Fact'[Billable Hours Worked] )
),
[Total Hours Worked] > 20
)
RETURN
COUNTROWS(TempTable)
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 |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |