Forum Discussion
How to Calculate Average While Keeping One Filter
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
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.
11 Replies
- parry2k
Super User
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.
- Ashish_Mathur
Hi,
Does this measure work?
=calculate(average(Data[performance id]),all(Data[Employee ID]))
- Ashish_Mathur
Hi,
Share the link from where i can download your PBI file.
- parry2k
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 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
johnt75Try
Department Average = var currentDepartment = SELECTEDVALUE('Employee'[Department]) return CALCULATE( AVERAGE( 'Data'[Performance ID]), 'Data'[Department] = currentDepartment )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.
- parry2k
Anonymous where is the link?
