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 like this:

 

The data is on sheets like this:

 

How can I create this line chart in Power BI?

 

Thank you in advance 🙂

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.

     

3 Replies

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

    Add a WeekNumber Column to sheet 1. Or better yet, a DATE

    So columns look like....
    Date, Pupil, Form, Reward

    Combine all the data to a single sheet, adding the Date to each entry. 

    Import to power bi. 

    If I answered your question, please mark my post as solution, Appreciate your Kudo

     

  • 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.