It's been a little over a year since my wife and I returned from our 3 month digital nomad excursion to South America (Peru, Chile & Argentina), and we find ourselves itching to travel again. This time we are considering Germany, France and the Czech Republic.
I tend to dislike touristy places and favor locations that have a genuine culture. Places like Athens and Rome are great, but there's always that sense of someone desperately trying to sell you something. And judging from recent years, this has only gotten worse with the increase in tourism worldwide, coining the term "overtourism". From Wikipedia, "Overtourism refers to a situation in which conflicts arise between locals and visitors at tourism destinations."
Casting aside the economic and cultural impact of tourism, let's take a look at visualizing the situation with Power BI.
What's in this Post?
In this post, I'll discuss my method for creating a dashboard and some tips for Power Query like, pivot/unpivot and dynamically referencing a column in Power Query with a parameter.
The Data
Worldbank.org has a juicy repository of collected data for the number of entries into every country (or at least most countries - I haven't confirmed if it includes all) from 1995 to 2017. They even provided a very nice line graph which functions very smooth. I think they did a very nice job with it. At any rate, they kindly provide the csv file which contains the data. I needed to do just a tiny bit of data transformation.
Pivot/Unpivot with Power Query
The csv file structure had each year (1995 to 2017) as a column. This is typically what you would want as an executive review or something, but it's not what we want for data visualization. We need a single column representing all years. This is where the unpivot function makes our life really easy.
I always get mixed up with the pivot and unpivot functions. Typically, I try one and hope it's the correct option. In this case, it is the unpivot method we want.
You just need to select all the years columns, and use the unpivot function:
Calculating the Percent Increase in Tourism
The most difficult part of creating this dashboard, was calculating the difference in the number of tourists between any given year. It's been a little while since I've written any DAX, so I had to take it one step at a time.
1. Calculate the Average Number of Tourists.
There's only one value per country for each year, so returning the AVG will return the singular value. I could've done the SUM as well, but using average is more interesting to me - particularly for comparative purposes.
Avg Number of Tourists = Average(TouristsByYear[Number of Tourists])
2. Calculate the Average Number of Tourists for the current Year.
To make it easy for myself, I hard-coded the current year of "2017" into the DAX formula. I'm also using the CALCULATE() function in tandem with ALLEXCEPT().
Avg Number of Tourists For Current Year =
CALCULATE([Average Number of Tourists],
FILTER(
ALLEXCEPT(
TouristsByYear, TouristsByYear[Country Name]),
TouristsByYear[Year] = 2017))
If you are unsure of what the Calculate() function is, feel free to read my blog about DAX stuff you should know.
ALLEXCEPT()
OK, what is the AllExcept() function? Before I attempt to explain that, let me say that I first tried the ALL() function. This worked initially, however, when I inserted a visual to see the numbers by country, all countries were showing the same value. This is because the ALL() function ignores the filter context in the visuals, and as a result applies the calculation to the full table (hence ALL).
The AllExcept function helps us here because we want to see the calculation take consideration of the countries. So we use the AllExcept function and pass in the "Country" field to preserve the filter context of this one field.
Lastly, I set the year to equal 2017, since that's the year I want to compare everything else with.
3. Create a Parameter for the Year
I created a simple parameter table which is used to select a year. Why not just use the year that's in our fact (data) table? Well, if we did that, the visuals would all filter to show a single year, which I did not want. Having a separate parameter table allows us to select any year without our visuals applying a filter (giving us more control of the dashboard). Here, I manually entered 1995 as a beginning value and 2017 as the end value.
4. Calculate Number of Tourists for Selected Year.
Now, we are going to use the parameter we just created to calculate the number of tourists for the parameters value. We can simply reuse the DAX formula we created for number of tourists in the current year, and replace 2017 with the parameter name.
Avg Number of Tourists For Selected Year =
CALCULATE([Average Number of Tourists],
FILTER(
ALLEXCEPT(
TouristsByYear, TouristsByYear[Country Name]),
TouristsByYear[Year] = SelectedYear))
5. Calculate the Difference
Now we can finally calculate the difference. And this is the easiest part. The DAX is simply a subtraction.
Avg Difference = Avg Number of Tourists For Current Year - Avg Number of Tourists For Selected Year
Avg Difference Percent = Divide(Avg Number of Tourists For Current Year, Avg Number of Tourists For Selected Year, 0)
I used DIVIDE() for the percent just in case there is no data for a country for the selected year.
Bonus Tip: Dynamic Column For Power Query
If for whatever reason you only want to see a specific year (or just a specific column for any situation you are working with) within Power Query, you can create a Power Query parameter for the column.
Let's say you would normally select the columns you want, and remove the rest. However, some of those columns that you removed may be used in the future. In this case, create a parameter for the column name. Then on the Power Query step to Remove Other Columns:
Just replace the column name with your parameter name (YearToShow in this example, which we set equal to 2017):
Cheers!
