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 

 

Thank you for your response. This is a solution, but not the solution I am looking for. The average grade for the units should be all together in one matrix table, showing the right average for each individual unit. 

I probably didn't explained it clear enough, so my apology for that. I still hope you are willing to solve this. 

Anonymous's avatar
Anonymous
Not applicable
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
    Not applicable
    6 years ago

    Hi  Anonymous  

     

    I just use 1 table,you can move the measure which is in another table into one table:

    Then create measures as below:

     

    _Average grade = 
var _sumx=SUMX(FILTER(ALLSELECTED('grade unit calculated'),'grade unit calculated'[Location code]=MAX('grade unit calculated'[Location code])),'grade unit calculated'[N=])
var _sumall=SUMX(FILTER(ALLSELECTED('grade unit calculated'),'grade unit calculated'[Location code]=MAX('grade unit calculated'[Location code])),'grade unit calculated'[multiple])
var _sumxtotal=SUMX(ALLSELECTED('grade unit calculated'),'grade unit calculated'[N=])
var _sumalltotal=SUMX(ALLSELECTED('grade unit calculated'),'grade unit calculated'[multiple])
Return
IF(ISINSCOPE('grade unit calculated'[Location code]),DIVIDE(_sumall,_sumx),DIVIDE(_sumalltotal,_sumxtotal))
    _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])),'grade unit calculated'[multiple])
Return
IF(ISINSCOPE('grade unit calculated'[Unit code]),DIVIDE(_sumalltotal,_sumxtotal),BLANK())

     

    For the related .pbix file,pls click here.

    If you have any new requirement,better create a new thread,kinda busy recently,and followed case will be delayed.

    Much appreciated.

     
     
    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 

     

    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