Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
philippa_f
Frequent Visitor

Need to calculate aggregated values based on slicer selections

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

1 ACCEPTED SOLUTION

5 REPLIES 5
daXtreme
Solution Sage
Solution Sage

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

 

 

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.

 

File attached

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

 

Philippa

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.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors