Learning how to edit table calculations yourself is an advanced capability, but it gives you the opportunity to create rich metrics, such as the percent difference from an average.
In the first example, we will modify the running sum of Remittances per Capita so that it includes the preceding and next three values, which can be done from the edit table calculation dialogue by entering those numbers in their respective places.
In the second example, we will modify the Remittances per Capita percent difference table calculation to show the percent difference from the average.
Let's begin with the first example. We'll modify the running sum in the following way:
WINDOW_AVG
function tells Tableau Public that the field can be addressed and we want it to apply the AVG
function to the values-2
and 0
with 3
and 3
respectively, as shown in the following screenshot. You can also create an integer parameter and allow users to set the number. We will show you how to do that in the chapter on parameters:The result is a calculated field that is much smoother than the original one.
In the second example, we'll calculate the percentage difference from the average Remittances per Capita. First, we will show you the original calculated field, and then, we'll show the changes that we made.
We duplicated the sheet on which we were working and then dragged the percent differences of the Remittances per Capita field to the Measures pane, where we renamed it to Remittances per Capita % Diff from Avg, as shown in the following screenshot:
In the formula box, Tableau Public calculates the value for each year, subtracts the value of the first year from it, and divides it with the value for the first year.
The following are a few new functions in this formula:
-1
, which means the previous value, but it could be anything. The other values are the FIRST() and LAST() functions.In order for this to be the percent difference from the average, change the references to the first value to the references to the WINDOW_AVG.
The new formula looks like the one shown in the following screenshot: