topic Re: Need to calculate aggregated values based on slicer selections in DAX Commands and Tips

Need to calculate aggregated values based on slicer selections

philippa_f — Fri, 11 Nov 2022 08:34:52 GMT

Hi helpful people

Aggregation confusion here...

I have a table of student results (one row per student attempt at a particular course), and am working on a report which allows admin staff to multi-select up to four courses in a slicer, and then see a table output for which students have the highest aggregate mark for that particular selection of courses. E.G. for a selection of courses Basketweaving1001, Basketweaving1002, RocketSci1001 and RocketSci2002 only, the visual should show the highest ranking aggregate scores in descending order, e.g. Joe Brown, Jane Smith, Tom Jones etc etc.

I've been trying to write a measure using SUMX and ALLSELECTED and TOPN but have been tying myself up in knots - can anyone help?

Many TIA

Philippa

Re: Need to calculate aggregated values based on slicer selections

daXtreme — Fri, 11 Nov 2022 12:37:23 GMT

This description is not clear.

"E.G. for a selection of courses Basketweaving1001, Basketweaving1002, RocketSci1001 and RocketSci2002 only, the visual should show the highest ranking aggregate scores in descending order, e.g. Joe Brown, Jane Smith, Tom Jones etc etc."

What is "highest ranking aggregated score"? Nobody knows... When you post a question on a forum, you should be maximally clear about what you want and how the logic should work. Pictures do help as well.

If in doubt about how to phrase a question to maximize the chances of getting a good answer, please read this: How to Get Your Question Answered Quickly - Microsoft Power BI Community

Re: Need to calculate aggregated values based on slicer selections

philippa_f — Mon, 14 Nov 2022 01:39:35 GMT

Thank you for taking the time to provide feedback, daXtreme

Highest ranking aggregate score related to the marks students receive for those four courses, all added together (i.e. Aggregated). So if a student received marks of (for example) 72, 68, 50 and 60 for those four courses, their Aggregated score would be the sum of those four, i.e. 250.

I then need to be able to rank students according to these scores, e.g. if student 1 gets 250, student 2 gets 320, student 3 gets 315, student 4 gets 244, then what I need to see is a table showing

Student 2 - 320

Student 3 - 315

Student 1 - 250

Student 4 - 244

I hope that is a little clearer to anyone who is trying to help.

Re: Need to calculate aggregated values based on slicer selections

daXtreme — Mon, 14 Nov 2022 10:55:32 GMT

File attached

Re: Need to calculate aggregated values based on slicer selections

philippa_f — Tue, 15 Nov 2022 01:27:31 GMT

Thank you daXtreme. I don't think I would have figured that out alone. Much appreciated.

Philippa

Re: Need to calculate aggregated values based on slicer selections

daXtreme — Tue, 15 Nov 2022 11:06:52 GMT

You would but maybe you would have had to study DAX a bit more 🙂 Everything is achievable by anyone. The only question is how much time it'd take.