Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Average grade

Hi everyone,   I have a measure to calculate the average grade. Since it was necessary to unpivot columns, it was more difficult. I calculated the grade also in an Excel-file, where columns weren't...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi  Anonymous ,

     

    Create a measure as below:

     

    _Average total = 
var _sumxtotal=SUMX(FILTER(ALLSELECTED('grade unit calculated'),'grade unit calculated'[Unit code]=MAX('grade unit calculated'[Unit code])),'grade unit calculated'[N=])
var _sumalltotal=SUMX(FILTER(ALLSELECTED('grade unit calculated'),'grade unit calculated'[Unit code]=MAX('grade unit calculated'[Unit code])),'average grade'[multiple])
Return
IF(ISINSCOPE('grade unit calculated'[Unit code]),DIVIDE(_sumalltotal,_sumxtotal),BLANK())

     

    And you will see:

    For the updated .pbix file,pls click here.

     

     

    Best Regards,
    Kelly
    Did I answer your question? Mark my post as a solution!
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi Anonymous 

     

    Thanks for the new measure. 

    Is there a way to show just one measure instead of having a table which need two measures?

    This new measure has a new problem: in my original database, unit code is in a different file (see image ). I tried to adapt the measure, but it didn't show any results. 

    I added this to the sample set:

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

     

    But I really hope I could use one measure to show the average for the units as well as for the locations. I hope you could help me once again. 

     

    Kind regards,

    Johan

     

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi  Anonymous ,

 

In excel,you are calculating the division of N=*location and sum(N),it is different from average,that is why you have different results.

In power bi ,if you wanna get the same result from excel,you'd better create 2 measures as below:

 

 

multiple = MAX('average grade'[N=])*MAX('average grade'[Grade])
_Average grade = 
var _total=SUMX(ALLSELECTED('average grade'),'average grade'[N=])
VAR _divide=SUMX('average grade','average grade'[multiple])
Return
IF(ISINSCOPE('average grade'[N=]),MAX('average grade'[Grade])*MAX('average grade'[N=]),DIVIDE(_divide,_total))

 

 

Finally you will see:

For the related .pbix file ,pls click here.

 

Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Anonymous  and Anonymous 

     

    Thank you both so much for your help. Anonymous , thanks for showing how the measure came to a grade of 8,11. 

    Anonymous the measure works perfectly :). I get the same results for the total score, but not for each individual location. Could you explain what is going wrong?

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi  Anonymous ,

       

      Sorry I was mistaken by your first post,I have corrected the measures as below:

       

      multiple = MAX('average grade'[N=])*MAX('average grade'[Grade])
      _Average grade = 
var _sumx=SUMX(ALLSELECTED('average grade'),'average grade'[N=])
var _sumall=SUMX(ALLSELECTED('average grade'),'average grade'[multiple])
Return
IF(ISINSCOPE('average grade'[N=]),DIVIDE('average grade'[multiple],_sumx),DIVIDE(_sumall,_sumx))

       

      Finally you will see:

      For the related .pbix file,pls click here.

       

      Best Regards,
      Kelly
      Did I answer your question? Mark my post as a solution!
      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi Anonymous 

        Thank you for the new measure. 

        I have the following result using your measure:

         

        I would really like it if the measure _Average grade for each location showed the right grade, so for example, location 900 would be a grade of 9 ((2*10+2*8)/4=9). Is this possible?