How to create a line chart from multiple excel sheets

Hi dankello 

You can import the three tables to power bi, then create a calculated table

Table = var a=SUMMARIZE(ADDCOLUMNS('Week 1',"Week","Week1"),[Pupil],[Reward],[Week])
var b=SUMMARIZE(ADDCOLUMNS('Week 2',"Week","Week2"),[Pupil],[Reward],[Week])
var c=SUMMARIZE(ADDCOLUMNS('Week 3',"Week","Week3"),[Pupil],[Reward],[Week])
var d=GENERATEALL(GENERATEALL(SUMMARIZE('Week 1',[Pupil]),SUMMARIZE('Week 1',[Reward])),{"Week0"})
return ADDCOLUMNS(UNION(a,b,c,d),"Score",IF([Week]<>"Week0",SWITCH([Reward],"Gold",3,"Sliver",2,1),0))

Then create a measure

Measure = MAX('Table'[Score])

Then put the following field of the table to the line visual

Output

Best Regards!

Yolo Zhu

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