Get the correct average by latest value

[Islam]

Hello Everyone

I have a HR project and i wanna display a matrix showing average of report grades for each year for each employee

in some cases for single report decision the executional management evaluates an employee of certain grade and then the higher authority decides to change this report grade which we record with a report grade modifying decision

in this case actually we will get 2 grades for the same report for a particular year so i should count the latest grade only and ignore the old one like the attached picture

in the following the green matrix with 95.5 is the correct one but the depicted picture captured from the PBIX file it show 95.67 which is wrong

here is how i calculated the correct average manually in Let's Start Field :

7 - Grade = 96

8 - 1st Grade = 96

8 - 2nd and last modified grade = 95

then we conclude the following result

96 (Dec No.7 for the first half of year ) + 95 (2nd and latest entered value for Dec No.8 for the 2nd half of year ) = 191 /2(unique count of Decisions) = 95.5

and here is how the measure in PBIX is being calculated the incorrect average in Let's Start Field :

7 - Grade = 96

8 - 1st Grade = 96

8 - 2nd and last modified grade = 95

then tyhe measure concluded the following result

96 (Dec No.7 for the first half of year ) + 96 (2nd first entered value for Dec No.8 for the 2nd half of year ) + 95 (2nd and latest entered value for Dec No.8 for the 2nd half of year ) = 287 /3(count of Decisions) = 95.67

Here is the sample files

Excel File 

PBIX File 

[FrankAT]

Hi @Islam 

first I created a new calculated table from your Let's Test measure:

With using the filter for Report Year and EmpID 1  I get the following result and the average is indeed  95,666666.:

For all other filtered Report Year I get only one row.

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

[Islam]

yes sir this is the problem i wanna count only the latest value for each report if there's more than one value for any particular report decision like decision number 8 

A working around using power query maybe required but i dun actually know how to do that 

And btw you can determine the latest value of any report using the max number of transaction serial index column in ftransactions table 

[Islam]

@v-lionel-msft