I am really stuck on this and was due to release the report today! so really hoping you can help.
The attached shows an average score for students, against a heirarchy of dimensions: student number, programme, department, faculty. Each student has only one score. Each student is studying 2 programmes: BI and one other.
I am trying to avoid double-counting the students at Faculty level while showing an accurate figure at programme and student level. see attached for 2 failed versions.
You are quite right but in an attempt to explain the problem I oversimplified my example. The link table is actually v large and I'm already loading it distinct. I will have another go at giving a more accurate example and get back to you.