Calculate Average with Filter

[Anonymous]

Hi, I'm very new to power BI and been doing well so far, but I'm stuck on the following subject:

I'm trying to calculate the average of cases closed by analists by worked day, but I only want to consider "Worked day" when the analist has closed 2 or more cases.

Columns:

- Date of Closure

- Analist Name

- Case ID (Each case has a unique ID)

The problem is: I dont want to exclude the cases closed on Non Worked Days

E.G.

In 30 days, a Analist has closed 130 cases

But, in the first 20 days, he closed 6 per day (Literally, not on average), 120 in total

In the last 10, he closed 1 per day (Again, not on average), 10 in total

So we would consider only 20 worked days, but I dont want to ignore the 10 cases closed on the non worked days.

Basically, I would want the average by day being 130 cases in 20 days.

Hope the message was not to confusing.

Thank You

[Anonymous]

Say only one analyst is visible in the current context. Then, the calculation would be:

-- measure you need first

[# Closed Cases] =
	DISTINCTCOUNT(
		FactTable[Case ID]
	)

-- 'Calendar' must join to the FactTable on a Date field 
-- (the other field is [Date Of Closure]
-- and be designated as Date Table in the model. Please make
-- sure you follow the rules of creating a proper Calendar.
-- Without a good calendar a model is almost worthless and
-- prone to errors.

[Analyst Average] :=
var __onlyOneAnalystVisible = HASONEFILTER( FactTable[Analyst Name] )
var __minNumOfCases = 2
var __numOfWorkedDays =
	COUNTROWS(
		FILTER(
			'Calendar'[Date],
			[# Closed Cases] >= __minNumOfCases
		)
	)
var _totalNumOfCases = [# Closed Cases]
RETURN
	if( __onlyOneAnalystVisible,
		DIVIDE( __totalNumOfCases, __numOfWorkedDays )
	)

If you'd like to adjust it for a set of analysts, then you'd have to define the logic of what you mean by a daily average for a set of analysts.

Best

Darek