HELP: How to create a Chart with multiple Academic Years and Pass/Fail values?

[Kenji]

Hello,

I've searched for a potential solution for hours and haven't been able to solve my issue, so hoping someone can help out here. Thanks in advance!

I have the following data (example):

Student Name	1st year	2nd year	3rd year	4th year	5th year	1st year passed	2nd year passed	3rd year passed	4th year passed	5th year passed
A	2015	2016	2017	2018	2019	Pass	Fail	Fail	Pass	Pass
B	2018	2019	2020	2021	2022	Pass	Pass	Pass	Pass	Pass
C	2017	2018	2019	2020	2021	Pass	Pass	Pass	Fail	Fail

I would like to create a chart (I am thinking a 100% Stacked Column Chart) where I can see a timeline (Years) in the X axis, and the percentage of Students that have 'Pass/Fail' in each year in the Y Axis.

For example, in 2018, Student A was a 4th Year Student and Failed, Student B was a 1st Year Students and Passed, and Student C was a 2nd Year Students and Passed. This would give me a 67% pass rate for the year 2018, and so on.

Many thanks!

[v-yueyunzh-msft · Accepted solution]

Hi , @Kenji 

According to your description, you want to "create a chart (I am thinking a 100% Stacked Column Chart) where I can see a timeline (Years) in the X axis, and the percentage of Students that have 'Pass/Fail' in each year in the Y Axis.".

Thanks for your sample data ,first of all, your table structure needs to be optimized in Power Query Editor, which is difficult to analyze in Power BI.

Here are the steps you can refer to :
(1)We can open the Power Query Editor and click "Advanced Editor" and put this M code in it to refer to  :

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIyMDSFUGYQyhxCWUAoSyAVkFhcDKTcEjNzEBRUEEzF6kQrOWFoMzIwMoBQhhDKCE0bVgpkljMul6AaidUQZFcimUXIj7g9B3NXLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Student Name" = _t, #"1st year" = _t, #"2nd year" = _t, #"3rd year" = _t, #"4th year" = _t, #"5th year" = _t, #"1st year passed" = _t, #"2nd year passed" = _t, #"3rd year passed" = _t, #"4th year passed" = _t, #"5th year passed" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Student Name", type text}, {"1st year", Int64.Type}, {"2nd year", Int64.Type}, {"3rd year", Int64.Type}, {"4th year", Int64.Type}, {"5th year", Int64.Type}, {"1st year passed", type text}, {"2nd year passed", type text}, {"3rd year passed", type text}, {"4th year passed", type text}, {"5th year passed", type text}}),
    Custom1 = Table.Group(#"Changed Type","Student Name",{"test",(x)=>
Table.FromRows(List.Zip({x[1st year] ,x[1st year passed]}))   &
Table.FromRows(List.Zip({x[2nd year] ,x[2nd year passed]}))   &
Table.FromRows(List.Zip({x[3rd year] ,x[3rd year passed]}))   &
Table.FromRows(List.Zip({x[4th year] ,x[4th year passed]}))   &
Table.FromRows(List.Zip({x[5th year] ,x[5th year passed]}))
 }),
    #"Expanded test" = Table.ExpandTableColumn(Custom1, "test", {"Column1", "Column2"}, {"Column1", "Column2"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded test",{{"Column1", "Year"}, {"Column2", "Result"}})
in
    #"Renamed Columns"

 Then we can get this table:

(2)Then we can create a table like this as a Legend:

We do not need create any relationship between two tables and we can apply the data to Desktop.

(3)We can create a measure like this:

Measure = var _class=MAX('Class'[Column1])

return
IF(_class ="Pass" , CALCULATE( COUNTROWS('Table') , 'Table'[Result]="Pass") ,CALCULATE( COUNTROWS('Table') , 'Table'[Result]="Fail"))

Then we can put the fields we need on the visual and we can meet your need:

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly