Create a new table using measures from a different table

[tdr426]

Hello,

I'm new to Power BI and data visualization.   I've tried to figure this out, but no success so I'm posting here.

I have a table like this (Table 1)

Item	2021 Score	2022 Score	2023 Score	2024 Score
item 1	2	3	0	2
item 2	1	3	2	1
item 3	3	3	1	2
item 4	0	2	1	1

The goal is to show scoring trend over the years... like this (Table 2)

Year	Total Score
2021	sum(2021 Score)
2022	sum(2022 Score)
2023	sum(2023 Score)
2024	sum(2024 Score)

I have created the 4 "sum" measures in Table 1, but I can't figure out how to create Table 2. 

Great minds of the Power BI Forum, how would you do this?
Thank you in advance.

Follow up 5 min later: I see I can get this data via a matrix.  I want to ultimately display the trend in a bar chart, though, not a tabular format.  I feel like what I want is a visualization based on a visualization.  Is that a thing?

[tdr426 · Accepted solution]

tl;dr; - Using "Unpivot" in Power Query was the key to what I needed.  Steps are

1. Select columns
2. Unpivot those columns
3. Clean up data.

Full answer:

Thank you to the people who answered. My original table is far more complicated than I indicated in my original question.  So using Transpose (per answer #1 above) created more problems than it solved.  It did not Transpose in a way that was helpful to me and I got stuck.

I'm enough of a newbie that I needed some hand-holding and I found some people in my workplace to help. 

I selected the 4 "score" columns in my table, and then did "Unpivot", which transposed the data just from those columns.  I then cleaned up the data via Replace Value, ie changed the value from "2021 Score" to "2021".

The team then showed me how to create a simple bar chart with the 2 new columns that resulted from the unpivot and voila!

[Selva-Salimi]

Hi @tdr426 

 you can write a measure as follows:

measure score :=

var selected_year = selectedvalue ( date [year])

var tbl = union ( selectedcolumn (your_table , "Score" ,[score 2021] , "year" , '2021' )

 , selectedcolumn (your_table , "Score", [score 2022] , "year" , '2022')

, selectedcolumn (your_table , "Score", [score 2023] , "year" , '2023')

, selectedcolumn (your_table , "Score", [score 2024] , "year" , '2024') )

 return

sumx ( filter (tbl , year = selceted_year) , Score )

If this post helps, then I would appreciate a thumbs up 👍 and mark it as the solution to help the other members find it more quickly. 

[sjoerdvn]

This is typically done in power query, not in DAX. check out this.