topic Create a new table using measures from a different table in DAX Commands and Tips

Create a new table using measures from a different table

tdr426 — Thu, 12 Sep 2024 14:26:42 GMT

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?

Re: Create a new table using measures from a different table

sjoerdvn — Thu, 12 Sep 2024 14:39:16 GMT

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

Re: Create a new table using measures from a different table

Selva-Salimi — Thu, 12 Sep 2024 14:45:08 GMT

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.

Re: Create a new table using measures from a different table

tdr426 — Thu, 12 Sep 2024 15:59:50 GMT

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!