How to track awards data over time?

[dankello]

Hi, I hope you can help. I have data which I am looking to feed into Power BI and present as performance of awards over time. Students can achieve Gold, Silver, Bronze or No Award (4th place) and i'd like to compare in a line chart the progress of each student by date. Gold will have the highest weighted value down to No Award. I am struggling to format this in Power BI. I've tried to transpose the excel data as per the below examples. Please could you let me know how I could achieve this? 

Thanks,
Daniel

[vs_7]

HI @dankello ,

kindly accept the solution if you got the output as required.

[vs_7 · Accepted solution]

HI @dankello ,

check below steps

• Load your data into Power BI, ensuring it contains columns for "Student," "Date," and "Award" (Gold, Silver, Bronze, No Award).

• Create a new table to define the weighted values for each award category. This table will be used for assigning numerical values to the awards. Let's call this table "AwardWeights" and define the columns as follows:

  • Award: Gold, Silver, Bronze, No Award
  • Weight: Assign numerical values corresponding to the weight of each award. For example, Gold: 4, Silver: 3, Bronze: 2, No Award: 1.

• Create a relationship between the "Award" column in your main data table and the "Award" column in the "AwardWeights" table.

• Create a measure that calculates the weighted value for each student based on the awarded level. You can use the following DAX formula for the measure, let's call it "Weighted Value":

  java
• Weighted Value = SUMX('MainData', 'AwardWeights'[Weight])

  This measure sums up the weighted values based on the awarded level for each student.

• Create a line chart visual in Power BI. Place the "Date" field on the x-axis and the "Weighted Value" measure on the y-axis.

• Add the "Student" field to the "Legend" or "Group" section of the line chart. This will display separate lines for each student, showing their progress over time.

[dankello]

Thank you @vs_7 😊 If I need columns for "Student," "Date," and "Award" (Gold, Silver, Bronze, No Award), then I would need to create separate sheets of information for each date? I can't think of a simpler way.