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

[Nelly]

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:

https://drive.google.com/file/d/1ix4hz6hSobEdxTGWYX6EaJ0zTh5sl26s/view?usp=sharing 

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.

[foodd]

Please provide your work-in-progress Power BI Desktop file (with sensitive information removed) that covers your issue or question completely in a usable format (not as a screenshot).

https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

This allows members of the Forum to assess the state of the model, report layer, relationships, and any DAX applied.

[Nelly]

hi @foodd,

thank for the reply. 

Fyi, i have updated my working file and expected of result in my original post at above