Forum Discussion
Create a multiple line chart per Country level
Hi All
I am learning power BI currently and had just started since 2 days ago. I am trying to figure out on how to create a line report which showing the values per quarter by country level.
Expected:
Sample data as follow:
| Country |
| 2019Q1 | 2018Q4 | 2018Q3 | 2018Q2 | |
| Vietnam | 5 | 4 | 3 | -2 | 1 | |
| Thailand | 6 | 7 | 8 | 5 | 3 | |
| China | 2 | 3 | 4 | 10 | 5 | |
| Cambodia | 5 | 4 | 3 | 2 | 1 | |
| Indonesia | -4 | -5 | 4 | 3 | 1 |
I couldnt get it works unless I transposed the table into below:
| Country | Vietnam | Thailand | China | Cambodia | Indonesia |
| 2019Q2 | 5 | 6 | 2 | 5 | -4 |
| 2019Q1 | 4 | 7 | 3 | 4 | -5 |
| 2018Q4 | 3 | 8 | 4 | 3 | 4 |
| 2018Q3 | -2 | 5 | 10 | 2 | 3 |
| 2018Q2 | 1 | 3 | 5 | 1 | 1 |
However if the table transposed as above, i couldn't add the country as filter, only quarter..
Is there any way to create the multiple Line chart per countries which filterable by the countries?
Thanks!
Hi Anonymous ,
at first you need to unpivot the last 5 columns of your original data ( > Transform > Unpivot columns) so that you get a table like this:
Country Quarter Value
Vietnam 2019Q2 5 Vietnam 2019Q1 4 Vietnam 2018Q4 3 Vietnam 2018Q3 -2 Vietnam 2018Q2 1 Thailand 2019Q2 6 Thailand 2019Q1 7 Thailand 2018Q4 8 Thailand 2018Q3 5 Thailand 2018Q2 3 China 2019Q2 2 China 2019Q1 3 China 2018Q4 4 China 2018Q3 10 China 2018Q2 5 Cambodia 2019Q2 5 Cambodia 2019Q1 4 Cambodia 2018Q4 3 Cambodia 2018Q3 2 Cambodia 2018Q2 1 Indonesia 2019Q2 -4 Indonesia 2019Q1 -5 Indonesia 2018Q4 4 Indonesia 2018Q3 3 Indonesia 2018Q2 1 With this query result you can create your multiple line visual and sort it by quarter:
Voila!
3 Replies
Hi Anonymous ,
at first you need to unpivot the last 5 columns of your original data ( > Transform > Unpivot columns) so that you get a table like this:
Country Quarter Value
Vietnam 2019Q2 5 Vietnam 2019Q1 4 Vietnam 2018Q4 3 Vietnam 2018Q3 -2 Vietnam 2018Q2 1 Thailand 2019Q2 6 Thailand 2019Q1 7 Thailand 2018Q4 8 Thailand 2018Q3 5 Thailand 2018Q2 3 China 2019Q2 2 China 2019Q1 3 China 2018Q4 4 China 2018Q3 10 China 2018Q2 5 Cambodia 2019Q2 5 Cambodia 2019Q1 4 Cambodia 2018Q4 3 Cambodia 2018Q3 2 Cambodia 2018Q2 1 Indonesia 2019Q2 -4 Indonesia 2019Q1 -5 Indonesia 2018Q4 4 Indonesia 2018Q3 3 Indonesia 2018Q2 1 With this query result you can create your multiple line visual and sort it by quarter:
Voila!
Thanks a lot for the solution! It works like a charm!
Mariusz Anonymous
Hi Anonymous
Please see the attached file with the solution.
All you need to do is use Unpivot on all Quarter column instead of the Transpose.
Best Regards,
Mariusz
If this post helps, then please consider Accepting it as the solution.
Please feel free to connect with me.
