Parameter-based Table Calculations
May 29, 2018Klaus Schulte
When I was playing around a little bit with this week’s #MakeoverMonday data on The World’s Most Expensive Prime Property I had the idea to compare prices for prime property in the selected cities with each other.
My first idea was to compare all prices of all cities with each other and therefore I built this crosstab/heat map:
However, I wasn’t quite satisfied with the result. My main concern was that it could be too hard to read for people who are not familiar with reading such crosstabs.
Eventually I remembered a viz which I first saw in a MakeoverMonday-challenge created by Michal Mokwinski.
— Michał Mokwiński (@michalmokwinski) 16. November 2017
Basically this chart visualizes only one column of my crosstab as a bar chart, but allows the reader to select a column.
I am glad that I resisted my first impulse to download Michal’s workbook and to reengineer his viz because creating this chart just needs a few easy steps:
1. Calculate $/sqm
In the first step I calculated the price per square meter by just dividing 1.000.000 by the square meters and built a bar chart sorting the cities in descending order by the calculated ‘$ per sqm’.
2. Creating the parameter
To select different cities it needs a parameter. In this case, I created a parameter based on the dimension ‘city’.
3. Add a table calculation
Then I added a quick table calculation ‘percentage difference’ on my calculated field ‘$ per sqm’ and made the following settings to compute by cities and relatively to my parameter.
4. Select ‘Show parameter control’ to bring in a dropdown menu to select the cities.
I wanted the labels to look like in Michal’s visualization. Therefore I first transformed my table calculation into a measure by just dragging the field from the column shelf to the measures shelf and naming it ‘difference in %’. Then I created a calculated field calculating -0.5 or +0.5 depending on whether the difference is positive or negative and added this field to columns.
The rest was just creating a dual axis, synchronizing the axes, changing the colours (to white for the ‘label axis’), bringing in the labels to both axes and doing some work on the tooltips.
This is the final result:
Hope you enjoyed this post!