Forum Discussion
Helper IHow to create a new table with measure
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!
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.⚡
7 Replies
- parry2k
Super User
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.⚡
left4pie2Thanks 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!
- 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.
