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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
tk0501
Frequent Visitor

Modify Value in row based on value in other row

Hello Power BI Community,
I have a table that looks like following. Example data set is found at the end.

| Student_Number | Exam_Number | Score1 | Score2 |

 

I am wanting to calculate / graph statistics based on this data set.
1. The average score of each exam
2. Rank exams based on sum of all Score1s in ascending order (Add all available score1 per exam. Rank them in ascending order)
3. Rank exams based on sum of Score2s in ascending order
4. After ranking the sum of exam scores, perform percentile calculation to find out what P10,50,90 scores are

I am very new to Power BI. I appreciate any advice on what I should read and how I can get started
Thank you,

 

Student NumberExam NumberScore1Score2
1064..
2090..
3021..
1122..
2142 
3183 
1272 
2220 
3270 
1342 
2389 
3369 
1 ACCEPTED SOLUTION
v-juanli-msft
Community Support
Community Support

Hi @tk0501 

Create measures

average score1 = CALCULATE(AVERAGE(Table1[Score1]),ALLEXCEPT(Table1,Table1[Exam Number]))

sum score1 = CALCULATE(SUM(Table1[Score1]),ALLEXCEPT(Table1,Table1[Exam Number]))

rank score1 = RANKX(ALL(Table1),[sum score1],,ASC,Dense)

8.png

 

Best Regards
Maggie

 

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

View solution in original post

2 REPLIES 2
v-juanli-msft
Community Support
Community Support

Hi @tk0501 

Create measures

average score1 = CALCULATE(AVERAGE(Table1[Score1]),ALLEXCEPT(Table1,Table1[Exam Number]))

sum score1 = CALCULATE(SUM(Table1[Score1]),ALLEXCEPT(Table1,Table1[Exam Number]))

rank score1 = RANKX(ALL(Table1),[sum score1],,ASC,Dense)

8.png

 

Best Regards
Maggie

 

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

Thank you so much, Maggie @v-juanli-msft 

This looks to be the solution I was looking for.

A few follow up questions,

 

Say I have expanded this dataset to 10,000 exams and 5 students. (the actual model is 10,000 iteration simulation)

Does using measures still make sense in that scenario?

I will be using this Power BI using a standard 2019 consumer laptop (not a data server)

 

In addition, I'm now working on a visualization of the data.

I would like [Staked Area Chart] of...

  • Axis - Rank Score1
  • Values - Each student's score for a given exam

For example - Exam #1 was the highest ranked exam.

I would like Student's 1, 2, and 3's score for Exam #1 to be plotted on the Stacked Area Chart.

 

Could you please help me with how I can do this in Power BI?

Thank you!

 

 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.