Forum Discussion

dankello's avatar
dankello
Frequent Visitor
2 years ago
Solved

How to create a line chart from multiple excel sheets

Hi,   For student rewards (Bronze, Silver, Gold). I need to create a line chart which tracks progress over several weeks. Each week data is on a separate excel sheet. I want the line chart to look ...
Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

     

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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.

 

Data-estDog's avatar
Data-estDog
Resolver II
2 years ago

This solution provides exactly what the OP is asking for but perpetuates bad design and increases maintenance of the solution over time (you need to add a new sheet, new calculation, and modify the calculated table every turn of a week!!!!!!). If this is a one off throw away project or just a fictitiuos inquery to give you what you need for a real situation you have no power over, then use it. Otherwise invest an hour into chaning the source data design as show in my previous reply. You will be glad you did.