How to Calculate Average While Keeping One Filter

[Anonymous]

The selected value of Employee ID from filter shows a Department value in the text label for Department field.

The ask is now to calculate the average of performance ID for the whole department of the employee.
My calculation below seems to be failing please advise

It's a single table "Data" where every field resides

[parry2k · Accepted solution]

@Anonymous try this measure

Dept Performance = 

VAR __deptId = MAX ( Data[Department ID])
RETURN
 AVERAGEX (
     FILTER ( 
        ALL ( Data ), 
        Data[Department ID] =__deptId 
    ) , 
    CALCULATE ( SUM(Data[Performance ID] ) )
)

✨ Follow us on LinkedIn and  to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.

[parry2k]

@Anonymous where is the link?

[Anonymous]

AM sorry Parry, updated my last post- it has the link now
Lmk if it doesn't work

[Anonymous]

@Ashish_Mathur , @johnt75 , @parry2k 
Shairng the file link- if you can please have a look and help
https://drive.google.com/file/d/1xkK35yYfZ_Opyg9vJKbUMQXni_2Nlc9Q/view?usp=sharing

[Ashish_Mathur]

Hi,

Based on the file that you have shared, the answer should be 4.  Try these measures:

Department of selected employee = MAX(Data[Department])

Dept Performance = calculate(average(Data[performance id]),FILTER(VALUES(Data[Department]),Data[Department]=[Department of selected employee]))
/*CALCULATE(AVERAGEX(Data,Data[Performance ID]),SELECTEDVALUE(Data[Department])
CALCULATE( AVERAGE(Data[Performance ID]),KEEPFILTERS(SELECTEDVALUE(Data[Department])))*/

Hope this helps.

[johnt75]

Try

Department Average =
var currentDepartment = SELECTEDVALUE('Employee'[Department])
return CALCULATE( AVERAGE( 'Data'[Performance ID]), 'Data'[Department] = currentDepartment )

[Ashish_Mathur]

Hi,

Does this measure work?

=calculate(average(Data[performance id]),all(Data[Employee ID]))

[Anonymous]

This is still showing the answer as 2.98 as shown below, whichj is the overall average irrespective of department

The answer should be 3.09 (Per Pivot Below) for Software Engineering Department Performance average

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[Anonymous]

@parry2k Sir, this is giving me the average of all the dataset- I want to limit it to give average of Performance ID of the department for of the selected employee ID
The dataset is a single table.
Please help again if possible

[parry2k]

@Anonymous try this:

CALCULATE ( AVERAGE ( Data[Performance Id] ), REMOVEFILTERS ( Table[Employee Id] ), VALUES ( Data[Department] ) ) 

 it might need tweaking because not sure how the model looks like

✨ Follow us on LinkedIn and  to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.