How to organize and display unstructured data

[bobbob123]

Hi,

I have the following unstructured data.

After loading it on Power BI. I'm not sure how to organise it so that my final visual looks something like this.

Basically, i need the score to be my x-axis, i need two secondary axis, the school (left hand side) and state (right hand side). The school data will be a column graph, where as the state is a line graph. 

I will also need a slicer where users can select what year and it will display both sets of data.

Thanks

[Anonymous · Accepted solution]

Hi @bobbob123 ，

Is the following image shown the result you want?

Based on the fact that your own data format is not very convenient for manipulation, I have modified the format to be this:

Immediately afterwards, we need to help us complete the calculation by creating the measure. A total of three measures need to be created, as the following shows:

school = CALCULATE(SUM(Sheet1[Value]),'Sheet1'[Type]="school")

state 2020 = CALCULATE(SUM('Sheet1'[Value]),FILTER('Sheet1','Sheet1'[Type]="state"&&'Sheet1'[Year]=2020))

state 2021 = CALCULATE(SUM('Sheet1'[Value]),FILTER('Sheet1','Sheet1'[Type]="state"&&'Sheet1'[Year]=2021))

Then put the three measure into visual, and put “year” into slicer, you can get what you want.

For better understanding , you can view this PBIX file.

Best regards,

Community Support Team Selina zhu

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

[bobbob123]

How did you manipulate the original data set to make it look like the one in your second image? 

[Anonymous]

Hi @bobbob123 ,

Have you solved your question?

As @selinaz  descibes, you can follow those steps to modify your data.

Best regards,

Community Support Team Selina zhu

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

[selinaz]

Hi @bobbob123 ,

Please refer the following steps:

1.click "use first row as headers"

2.select the first column and click on the drop-down arrow for "unpivot column" and select "unpivot other columns"

3.still select the first column after unpivoting, and then click "split column"---"by delimiter"

4.finally, you can change the column name to what you want and you can get the second image as the above shows.

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

Best regards.