Forum Discussion
Power Bi Customised comparison bar chart
I have a list of students with their marks based on three subjects. Students are from various cities.
| Student Name | City | Subject | Marks |
| Student 1 | City 1 | Maths | 96 |
| Student 1 | City 1 | Chemistry | 56 |
| Student 1 | City 1 | Physics | 89 |
| Student 2 | City 1 | Maths | 56 |
| Student 2 | City 1 | Chemistry | 89 |
| Student 2 | City 1 | Physics | 45 |
| Student 3 | City 2 | Maths | 78 |
| Student 3 | City 2 | Chemistry | 25 |
| Student 3 | City 2 | Physics | 96 |
| Student 4 | City 2 | Maths | 85 |
| Student 4 | City 2 | Chemistry | 75 |
| Student 4 | City 2 | Physics | 45 |
| Student 5 | City 1 | Maths | 78 |
| Student 5 | City 1 | Chemistry | 95 |
| Student 5 | City 1 | Physics | 46 |
| Student 6 | City 2 | Maths | 85 |
| Student 6 | City 2 | Chemistry | 78 |
| Student 6 | City 2 | Physics | 98 |
I wanted to show a comparison bar graph of a student with other students depending on the selected student city.
If I selected Student 1, then I should get all other students of Student 1' city(City 1) and compare their marks based on their marks like Student 1 mark vs the aggregate of other students' marks.
I am able to bring the chart like above comparing student with other students. Only the problem now it should be compared the student with other students where the city is only the selected student city.
If I choose Student 1, it should take only the students of the City 1 for the comparison.
I transformed the table like below,
Students + Others =
UNION(
VALUES(Sheet1[Student Name]),
ROW("Student Name", "Others")
)
Created another mesaures like the below,
Marks vs Others =
VAR CurrentStudent = MAX('Students + Others'[Student Name])
VAR SelectedStudents = ALLSELECTED(Sheet1[Student Name])
VAR PossibleStudents = ALL(Sheet1[Student Name])
RETURN
SWITCH(
TRUE(),
CurrentStudent IN SelectedStudents,
CALCULATE(
[Total Marks],
Sheet1[Student Name] = CurrentStudent
),
CurrentStudent = "Others",
CALCULATE(
[Total Marks],
FILTER(
ALL(Sheet1[Student Name]),
Sheet1[Student Name] IN PossibleStudents
&& NOT(Sheet1[Student Name] In SelectedStudents)
)
)
)
I made the chart now like the below fields
Now my only problem is, If I select Student 1, it should take the students of the city what the city of Student 1.
In my case it should take only Student 2 and Student 5 for the comparison for Student 1.
3 Replies
Hi Heartlyforjesus ,
Do you also need to explain what kind of output you expect to get at the end? When you select student 1, is the comparison the average of the scores of student 2 and student 5?
Best Regards
v-zhangti , Yes. Since Student 1, Student 2 and Student 5 are from same City 1, I would like to compare Student 1 only with Student 2 and Student 5. It means the student should be compared with other students who are from the same city of selected student.
If I select, Student 3, then average mark should be compared with Student 4 and Student 6. Because they are from same City 2.
What i understood from the conversation is that , you can use Power BI slicer to slice the data on city and use the following custom visual from Microsoft appsource to plot your data. I hope it helps.
