Seemingly Simple Problem With Counting Rows

[jl20]

Hi all,

I have a fairly straightforward requirement that I can't seem to crack. Here's the situation:

Data modelFact

Visual

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!

[Greg_Deckler]

Use a measure that leveages SUMMARIZE to build a temp table as a VAR that mirrors you table visualization. Then simply use FILTER to filter that down and return what you want.

[jl20]

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)