Forum Discussion
Create a new table using measures from a different table
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?
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!
3 Replies
This is typically done in power query, not in DAX. check out this.
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.
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!
