topic Seemingly Simple Problem With Counting Rows in Desktop

Seemingly Simple Problem With Counting Rows

jl20 — Thu, 12 Jul 2018 14:42:20 GMT

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!

Re: Seemingly Simple Problem With Counting Rows

Greg_Deckler — Thu, 12 Jul 2018 16:36:21 GMT

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.

Re: Seemingly Simple Problem With Counting Rows

jl20 — Thu, 12 Jul 2018 17:28:41 GMT

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)