How to create a new table with measure

[left4pie2]

I'm trying to make a dashboard for some quality control forms. As you can see below on the form, the number of defects for each category are recorded.

I'm trying to figure out a way to transfrom this data to create a table with three columns as show in the picture below.

I was thinking of doing this with measures so that when different dates, shifts, ect are filtered it changes the new table. 

Thanks!

[parry2k]

@left4pie2 in a scenario like that you want to make this as a separate table without any duplicates, and then have another table for response with unique response id which will have a relation with these both tables, and then all the calculations will be super simple.

✨ Follow us on LinkedIn and  to our YouTube channel

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

[parry2k]

@left4pie2 Glad it worked out. Take a moment to subscribe my YouTube channel. There will be lot more interesting stuff there. Link below.

✨ Follow us on LinkedIn and  to our YouTube channel

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[left4pie2]

Hey parry2k I have a follow up question.

I've unpivoted my data like you said to make the two columns on the far right called "Defect Name" and "Defect #" My problem now is if I want to sum my other columns like "Total Number of Planks in Boxes", I have duplicate values. I do have a response ID column. Is there a way to sum the Total Planks in Boxes by filtering out duplicates with response ID? The picture below shows the desired results.

 

[parry2k · Accepted solution]

@left4pie2 I would recommend unpivoting the table and then visualizing it instead of creating a new table.

✨ Follow us on LinkedIn

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[left4pie2]

Thanks parry2k, this is exactly what I needed! You've saved me from alot of head banging.

[amitchandak]

@left4pie2 , You need to create custom table like this to split measure to dimension with all filter dimension and use that

example, new table code

union(
summarize('Table',"Measure","Min","Test1",min('Table'[Test1]),"Test2",min('Table'[Test2]),"Test3",min('Table'[Test3]))
summarize('Table',"Measure","Max","Test1",max('Table'[Test1]),"Test2",max('Table'[Test2]),"Test3",max('Table'[Test3]))
)

or

union(
summarize('Table',"Measure","sales","This period",[SALES YTD],"Last period",[SALES LYTD],"POP",[SALES YOY])
summarize('Table',"Measure","unit","This period",[unit YTD],"Last period",[unit LYTD],"POP",[unit YOY])
)

or

union(
summarize('Table',"Measure parent","Measure1","Measure",[Measure1])
summarize('Table',"Measure parent","Measure2","Measure",[Meausre2])
)

[left4pie2]

Thanks!