Forum Discussion

Islam's avatar
Islam
Icon for Helper V rankHelper V
5 years ago

Get the correct average by latest value

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
 
Need Help

3 Replies

  • FrankAT's avatar
    FrankAT
    Icon for Community Champion rankCommunity Champion
    5 years ago

    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's avatar
      Islam
      Icon for Helper V rankHelper V
      5 years ago

      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