Forum Discussion

Nelly's avatar
Nelly
New Member
3 years ago

Need Urgent Help on Calculate Measure Data based on Date Range Selected in Slicer

Hi,

I have 2 set of tables which consist of safety assessment:

 - Assessment Data table is my row data

 - Assessment Filter Data table is the coming from row data that i have filter out some duplicate data in order to calculate the average score based on date range selection.

 

I need to categorize the employee into Strongest and Weakess employee based on the their group average compare with the average score based on date range selected as below screenshot:

 

I create 2 new columns to indicate the strongest and weakest based on below formula:
Strongest = IF( 'Assessment Data'[GroupAverage]>=  AVERAGE('Assessment Filter Data'[group average]),"Yes","No")
Weakest = IF('Assessment Data'[GroupAverage] < AVERAGE('Assessment Filter Data'[Group Average]),"Yes","No")
 
Without filter the date range, the result is correct. But, when filter the date range, the result shown wrong as Some employee should be move to strongest but it still indicate as weakess as per above screenshot.
 
Here is my working file:
 
 
Pre-condition: selected date range from "13 Apr 23- 29 Apr 23"
The "Safety Average score" will be change from 4.25 to 3.82
The expected Result is:
- All of the Employees that the score is below 3.82 the weakest column will be = Yes;
- All of the Employees that the score is above 3.82 the Strongest column will be = Yes;
 
Appreciate If teams can help me to resolve the issue that i faced.
 
Thank you so much in advance.
 

 

 

Need Help

2 Replies