cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper II
Helper II

Best way to split a single table into 2 views

Hi all,

I have a "student" table and a "test results" table. The test table contains results 2 types of tests - a math test (MTest) and an English test (RTest). The tests are undertaken once per year. I need to plot the annual results in a single dot plot with the Rtest as the X and the MTest as Y (ie one dot per student) [a fuiture enhancement. The tables are joined via a simple StudentID.

What would be the best practice approach to split & plot the data? A set of virtual tables represening each of the tests? Or is there a more elegant/efficient way to achieve the above?

I am still a baby in the woods as far as Power BI is concerned - but would like to learn and adopt best practice as I progress. Just a pointer/opnion would be greatly appreciated.

Thanks in advance,

Lex

 

1 ACCEPTED SOLUTION
Community Support
Community Support

Hi, @lutho 

According to your description, I guess that you want to split your single table into the 2 dimensions and show the data in the scatter chart, you can follow my steps:

This is the test data I created based on your picture:

v-robertq-msft_0-1609121139367.png

 

  1. I click “Transform data” to go to the Power query editor, select the table, and click “Piovit column” then set like this:

v-robertq-msft_1-1609121139423.png

 

  1. And I get the data you want to get, like this:

屏幕截图 2020-12-28 100614.png

  1. Then I click “Apply and close” and go to the report view to create a scatter chart then place columns like this:

屏幕截图 2020-12-28 100620.png

And you can get what you want.

You can download my test pbix file here

 

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

7 REPLIES 7
Helper II
Helper II

Well - I asked for the right and elegant solution and I got it. I had been playing with the "duplicate" option, however the pivot column option is just right and works a treat. Thanks Robert!

Community Support
Community Support

Hi, @lutho 

According to your description, I guess that you want to split your single table into the 2 dimensions and show the data in the scatter chart, you can follow my steps:

This is the test data I created based on your picture:

v-robertq-msft_0-1609121139367.png

 

  1. I click “Transform data” to go to the Power query editor, select the table, and click “Piovit column” then set like this:

v-robertq-msft_1-1609121139423.png

 

  1. And I get the data you want to get, like this:

屏幕截图 2020-12-28 100614.png

  1. Then I click “Apply and close” and go to the report view to create a scatter chart then place columns like this:

屏幕截图 2020-12-28 100620.png

And you can get what you want.

You can download my test pbix file here

 

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Community Support
Community Support

Hi, @lutho 

According to your description, I think the scatter chart in Power BI can perfectly fit your requirement. Below is my test data and my steps to achieve this chart, you can take a look and find if it’s useful to you:

  1. I created some data and create a relationship based on your description:

v-robertq-msft_0-1608625336973.pngv-robertq-msft_1-1608625336977.png

 

v-robertq-msft_2-1608625336979.png

 

Then I created a scatter chart and place columns like this:

v-robertq-msft_3-1608625336986.png

 

And I guess this is similar to the chart that you want to get.

You can download my test pbix file here

 

More info about scatter chart in Power BI

 

If this result is not what you want, you can post some sample data(without sensitive data) and your expected result.

How to Get Your Question Answered Quickly 

 

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thanks Robert, the scatter chart is the right visual. My issue relates to the approach/method of how my single table with test results can be split into the 2 dimensions required. My apologies and I should have been more clear and provided a data set. The issue is it is a single table that holds both data sets and the question is for the best approach to split the data into both dimensions?

Thanks for responding!

 

2020-12-28_11-08-33.jpg

 

 

Super User III
Super User III

Hi @lutho 

Download example PBIX file with the following visuals and tables.

A scatter plot will show you how two variable relate to each other, in tis case the two test results, but then you can't relate those to the students.

If you want to plot multiple test scores and be able to see what each student scored, I'd use a Clustered Column chart.

Starting with a sample table like this

test-results.png

You'd get a visual like this

clustered-col1.png

 

 

If later you add a table containing Student Names and ID's like this

student-tab.png

 

you can link the two tables with a 1-to-many relationship

1-m-rel.png

 

Allowing you to create a visual with the student names on the x-axis

clustered-col2.png

 

Regards

Phil


If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


Thanks Phil for taking the time for the ellaborate answer and you point regarding the need for the variables to relate to each other is valid. In this case there is actually a relationship - the test are an ability type of test where the ability of the numeric side is plotted against the verbal/linguistic side. The plot help staff identify and use appropriate learning methods depending where the student sits on the plot. They are definitely related! I hope that makes sense?

Super User II
Super User II

Hey @lutho ,

Regarding your question, I have to admit that I have my problems understanding your requirement.

Please consider to create a pbix file that contains sample data, but still reflects the data model (https://docs.microsoft.com/en-us/learn/modules/design-model-power-bi/).
You can use this to enter data directly in Power BI Desktop: https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-enter-data-directly-into-desktop. Upload the pbix file to onedrive or dropbox and share the link. If you are using Excel to create the sample data, share the xlsx as well.

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors