Average of score by cateogry/filter

[Aimeeclaird]

I have some questionnaire data that I am trying to create a specific visualization for. 

On my dashboard I want to have a slicer for each student. I then want to show in a visualisation what the student scored for each question, compared to the average score at their school and also in their Local Authority. 

So far, I have copied the main data set and removed the unnecessary columns. I have then selected all 12 assessment question columns and unpivoted them. 

For my Clustered Column visualisation, what should I include as the Values? 

I have tried creating 3 tables, one for Student, School and Local Authority however when I select a student, the School and Local Authority averages are identical...

I've attached a copy of the PBI document as it is all dummy data at the moment. 

https://data2actionltd-my.sharepoint.com/:u:/g/personal/aimee_laird_data2action_co_uk/EcH29jHt9dxJvQ...

Any advice would be welcome!! 

[HotChilli · Accepted solution]

There's a lot going on here.  I'll address my comments to the Clustered Column Chart and the student slicer.

When you see identical values like this, it usually means a problem with relationships and/or the data model.

There are 3 Fact tables being queried which more-or-less have the same info in them.  The Student slicer filters all of them successfully but the 'Assessment Question' on the x-axis comes from one table (student assessment).  The other 2 tables have the field duplicated but the Student Assessment table doesn't filter the other 2 fact tables.  That's why you get the same values across the chart.

I'm not sure what the overall plan for the report is but I think I'd start off with one Fact table with the Scores.

Once that's in place, you can write measures for the averages (remember that the visual is being filtered by student at the moment)

[Aimeeclaird]

Hi @HotChilli 

Thanks for your reply. 

Whilst waiting for a response I realised the model was a mess so spent some time tidying that up. 

I now have 1 table with the assessment scores pivoted. In that table i've included the student code, school code and local authority.

A couple of follow up questions (sorry!)

1. In my Fact table, should I unpivot the assessment questions? 

2. Regarding your suggestion of using measures to calculate the local authority and school measures, would I therefore have to do a measure for each school and each Local authority? 

Also, would the measure be something along the lines of:

BA Average = CALCULATE(

AVERAGE('Student Assessment Data'[Assessment Score]),

FILTER('Student Assessment Data', 'Student Assessment Data'[School Code] = "BA"))

I'm not sure how to write a measure that calculate the overal School / Local Authority average score per question.

Thanks in advance!

[HotChilli]

Q1. I think yes.  Please bear in mind I'm now constructing your data model in my head and it might not be what you have in reality.

Powerbi prefers columnar data.  If the questions each have their own column, you'll be writing individual measures  to calculate averages and I don't think you want that.

2. In the pbix you provided, you've already written measures for the averages by selecting the aggregation using the interface.  It's usually always better to write explicit measures unless the model is very simple.  You have control.  My point was to make sure you understand what is going on in the visual e.g. in the pbix, it didn't matter whether MAX, MIN or AVERAGE was chosen for the Student Assessment Score field, since the table was filtered by student and question(in the visual), it's the same result.