Forum Discussion
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 unpivot, and there is some difference. In PBI, the measure returns a grade of 8,11, while in Excel the grade is 8,21.
I can't figure out why there is a difference, so I hope someone could help me.
The link to the Excel-file:
https://drive.google.com/file/d/1ozNX8bghkW8VMAyiBphr-pSVyQSlrnsu/view?usp=sharing
The link to the pbix-file:
https://drive.google.com/file/d/1jB9TfYCL94TRspyfMP6EwhNnC8zes6vS/view?usp=sharing
(you need to open the ZIP extractor before you can download the file).
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,
KellyDid I answer your question? Mark my post as a solution!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
20 Replies
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,
KellyDid I answer your question? Mark my post as a solution!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,
KellyDid I answer your question? Mark my post as a solution!
You need to grant public view access to those files, so we can see them. 😊
Hi speedramps
Thank you! I wasn't aware that the access was limited. I have changed it, so it should be fine now.
