Transposing measures

[Anonymous]

Hi,

So I have a table in powerbi that uses measures.  It does it to get a students percentage of maximum marks for each subject that they study.  I needed to use a measure so that I could get the maximum possible mark for a subject filtered from another table.  I am then once again measure those into another table to get the average of their marks for all subjects using the following formula:

Academic = AVERAGE('dm SIS_AcademicReportGrade'[PercentOfMaximum])

This on it's own works well as I can see the average academic result of students both individually and as a group in a table.  This same table will also end up having another column which is the average attendence.  A measure of behaviour and a few other columns for each student.  As I will be able to set the date ranges these will be measures so that these values change via slicer.

I then wish to transpose this table so that I get for each student:

Student Category Value

John      Academic 0.75

John     Behaviour 1.0

John     Attendance 0.80

Mary   Academic 0.8

Mary   Behaviour 0.9

Mary   Attendance 0.2

This is so that I can read this all into a radar chart.  This will allow me to choose the individual student and see what their radar chart looks like on a number of different categories.

Following these instructions: https://www.youtube.com/watch?v=rpzU8qooOvQ

I am able to get a single value for academic for the entire school but not a row for each student.  What am I missing?

Thanks

[Anonymous]

Yeah I was hoping that it would work with slicers.  So that I could just choose the student from the slicer and display their results.  

[v-angzheng-msft · Accepted solution]

Hi, @Anonymous 

The measure value is based on calculation. When there is no context, the measure value will calculate the total value. You cannot get a single value without the context of the table.Also in the video, the three measures are also the total value of the three colors.

The transposition described in the video actually uses some functions to imitate the effect of transposition, and all parameters are hard-coded.

If you want to create a single subject score for a single student, you need to add more filter conditions to the measurement value, for example: measure= calculate(sum[marks], student="john", subject ="math").

This means that you may need to establish separate measure for each student's mark in each subject.

Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.