Search code examples
powerbicomparisonbar-chart

Power Bi Customised comparison bar chart

I have a list of students with their marks based on three subjects. Students are from various cities.

enter image description here

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.

enter image description here

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,

enter image description here

I created the Student + Others table

enter image description here

Students + Others =
UNION(
VALUES(Sheet1[Student Name]),
ROW("Student Name", "Others")
)

Created another measure like the below,

enter image description here

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

enter image description here

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, where it should take only Student 2 and Student 5 for the comparison for Student 1.

Solution

  • I hope below code will work for you. Please modify/optimize code as per your actual requirement. Create Measure:

    OthersTotalAllSubject = 
var selectedCity = SELECTEDVALUE(School[City])
var OtherTotal = IF(ISBLANK(selectedCity),CALCULATE(sum(School[Marks]),ALL(School[StudentName])),
 calculate(sum(School[Marks]),FILTER(ALL(School[StudentName],School[City],School[Marks]), selectedCity = School[City])))
var selectedStudentMarks = SUM(School[Marks])
return OtherTotal - selectedStudentMarks

    Create another measure

    SelectedStudentTotalAllMarks = CALCULATE(sum(School[Marks]))

    Put both measure in VALUES of bar chart & 'Subject' on AXIS.

    Student1 vs AllStudentofSameCity Student3 vs AllStudentofSameCity

    Please let me know, if you required any information about this solution.