Forum Discussion
Create a new table using measures from a different table
tl;dr; - Using "Unpivot" in Power Query was the key to what I needed. Steps are
- Select columns
- Unpivot those columns
- 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!
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.