How to pivot multiple columns into Matrix..

[pucities]

My data source looks something like this:

StudentID	Test1Score	Test1Avg	Test2Score	Test2Avg	Test3Score	Test3Avg
1	75	75	86	80	87	68
2	60	75	96	80	69	68
3	74	75	74	80	79	68

I can't use Test#Avg as measures for some reasons. 

When I choose StudentID=1 in the slicer, I want the matrix to show like this:

StudentID = 1
Test #	StudentScore	AvgScore	VarianceToAvg
Test1	75	75	0
Test2	86	80	6
Test3	87	68	19

How can I achieve this? Is there a way to do it without creating additional tables?

Thank you in advance!

[amitchandak · Accepted solution]

I think the better option is to unpivot data

https://radacad.com/pivot-and-unpivot-with-power-bi

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

[v-lili6-msft · Accepted solution]

hi @pucities 

You could refer to this pbix file:

Regards,

Lin

[amitchandak · Accepted solution]

I think the better option is to unpivot data

https://radacad.com/pivot-and-unpivot-with-power-bi

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin