Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Change Column Values When Filter Is Applied

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 mo...
  • Anonymous's avatar
    Anonymous
    7 years ago
    -- 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's avatar
Anonymous
Not applicable
7 years ago

 

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's avatar
Anonymous
Not applicable
7 years ago

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

 

Best

Darek

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago
    -- 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's avatar
      Anonymous
      Not applicable
      7 years ago

      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's avatar
        Anonymous
        Not applicable
        7 years ago

        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