Some types of table calculations allow the end user to define what the calculation is relative to. For instance, if you are calculating a ‘Difference From’ you can specify if this should be calculated relative to the first, last, previous or next value of your Table Calculation.
This choice can be done from the Table Calculation pop-up, or by right clicking on the specific measure pill. But how can you make this choice dynamic? perhaps controllable from a drop-down menu?
It is possible to do so using a Parameter. To find more details about the set-up of parameter check my blog post on that topic.
The parameter should be set-up in this way:
Data type: String
Allowable values: List
Populate the list using the relative values you wish to display. In this example I have used all 4 standard values: First, Last, Previous and Next.
Now drag the measure onto which you want to apply the Table Calculation onto either the Rows or Column shelves.
Right-click onto the measure pill, add and set-up the Table Calculation.
Holding CTRL (or CMD) drag and drop the measure back to the Measures section of the side bar. This will create a calculated field containing the syntax of the Table Calculation.
Right-click onto this new calculated field and press Edit…
Locate the offset of the LOOKUP function of this calculation, it would be either: 1, -1, FIRST() or LAST().
Now edit the offset using the following formula:
(IF [Your Parameter Name]= ‘First’ THEN FIRST()
ELSEIF [Your Parameter Name] = ‘Last’ THEN LAST()
ELSEIF [Your Parameter Name] = ‘Previous’ THEN -1
ELSEIF [Your Parameter Name] = ‘Next’ THEN 1 END)
Should you only wish to use 2 or 3 values, adjust the formula accordingly, make sure that your first value part of the formula starts with an IF rather than ELSEIF.
That’s it. Now the parameter control will allow to dynamically change the Table Calculation relative value.
