Normalize a series by first value in Tableau

Question

I'd like to normalize a data series in Tableau by the first value.

If my input table is:

date | amount
1/1   $100
1/2   $150
1/3   $250

I'd like to create (and plot) the normalized values:

date | amount_norm
1/1   1
1/2   1.5
1/3   2.5

In SQL this would be

SELECT date, amount / ( SELECT amount 
                        FROM table 
                        WHERE date = ( SELECT MIN(date) FROM table ) ) AS amount_norm
FROM table

Ideally, amount_norm should update as the user changes the selected time interval in the Tableau dashboard filter.

Seems pretty simple, but I'm totally unfamiliar with Tableau syntax. I've tried playing with FIRST() = 0 but don't really know what I'm doing.

I've found a similar question, but this didn't get me quite where I wanted.

Any help would be greatly appreciated!

Answer 1

To match the SQL code logic:

    SUM([Amount])
    /
   SUM({FIXED : SUM(IF [Date] = {FIXED : MIN([Date])} THEN ([Amount]) END)})

If you want it to adhere to filters in your workbook/dashboard you would add the dimension you're filtering on before the colon like below:

    SUM([Amount])
    /
   SUM({FIXED [filter dimension] : SUM(IF [Date] = {FIXED : MIN([Date])} THEN ([Amount]) END)})

To add more criteria for the fixed LOD expression you can add a comma and then another dimension.