Change Column Values When Filter Is Applied

[Anonymous]

Hi,

My main goal is to calculate the average of cases closed the employees have per day, but I'm only counting the days they close more than 2 cases.

So, If the date of closure is repeated more than two times on the table, theres a column representing it as 1.

1 for working days and 0 for non working days

The problem is:

When I filter a analist, the working day column doesnt filter.

So, If a day is marked as working day in the big picture, it will always be a working day even if the analist hasnt closed more than 2 cases.

Is there a way to change it?

[Anonymous]

Not enough info on the model and how it works currently. An example with some pics would be helpful. Please, try to understand that you've been working with the model for some time now and you do understand it. Others don't and thus need to be given much more info than you'd need to be able to understand.

Thanks.

Best

Darek

[Anonymous]

I think I´ve made some progress on my own, basically, all I need now is to have a correct total on the "Worked" Column.

How It Works:

The "Worked" measure is set to be "1" everytime the "Cases" column is bigger than 2.

What that says: Everyday that there were more than 2 cases, is considered a worked day.

I want to Sum the ones on the "Worked" Column to be able to divide the total cases by the total of worked days

[Anonymous]

I've got the solution... bear with me, please.

Best

Darek

[Anonymous · Accepted solution]

-- 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, __numOforkedDays )
	)

[Anonymous]

First of all, Thank you so much!

Second, sorry about the long time without accepting it as a solution, things are crazy here.

And last, Do you know what should I do if i wanted to see the analist average for more then one at once?

For example, I have 15 analists here, I want to know the average of their averages.

Thank you again.

[Anonymous]

Of course I do 🙂

[Analyst Average 2] =
var __result =
	AVERAGEX(
		VALUES( FactTable[Analyst Name] ),
		[Analyst Average]
	)
return
	__result

If only one analyst is visible in the current context, this measure will return exactly the same value as [Analyst Average] (the prior measure). So, you could treat this one as an extension of the previous one and hide the previous one (also rename both). The rule is that if a measure starts with _, then it should be treated as an internal model measure that should not be exposed to the end user. So, you'd name [Analyst Average] to something like [_AnalystAverage] and the latest one to [Analyst Average].

Best

Darek

[Anonymous]

Guess I'm not done yet

I´m trying to separate the Analyst Average by month.

But the average of the months together are not matching.

In this example, I wanted the total to be:

(9,35 + 7,76 + 7,44)/Number of months = 8,18

[Anonymous]

No mate. This is not the way the measure was designed to work. For each analyst, it first creates the average using the current filter context on dates. Then it averages over the analysts. What you are now trying to do is completely different. You will need a different measure for this.

But the new measure would have to work only in case full months are visible in the current context. To give a full description of a measure you have to say what it will calculate in all possible circumstances. You are saying "divide by the number of months." What if the current filter context does not contain full months? Have you thought about it?

Sorry 😞

Best

Darek

[Anonymous]

Got it!

I've managed to do what I wanted, here's how: 

Average of Analyst Average by month = 
AVERAGEX(
	KEEPFILTERS(VALUES('Calendar'[Month]));
	CALCULATE([Analyst Average])
)

Now it looks like this(The final result is the average of the months):

Thank you, again!

[Anonymous]

Hi there.

Well, the measure could be written simpler:

Average of Analyst Average by month = 
AVERAGEX(
	VALUES( 'Calendar'[Month] );
	[Analyst Average]
)

But it'll only calculate the correct average in certain circumstances, not always, so be warned!

Best

Darek

[Anonymous]

I love you man, You have no ideia how much you've helped me hahah

Wish you all the best.