Here is one possible approach.
You can create a fake dimension with the measure names in a separate datasource.
-In Excel, create one column with the measure names, Copy Cells, in Tableau new Worksheet and paste.
Since your filtering is based on a parameter, you can apply it directly in the calculations. So in all sources, create a calculation, for example if [Recent Time Period] then [Sales] end.
From the new datasource, create a new worksheet with the dimension [MeasureName] (the fake one) on the Rows.
And create calculations for your Total and budget values for example if ATTR( [Measure Name] ) = 'Sales' then SUM([Sample - Superstore].[Sales (adj)]) end
You can then play with all values in a single worksheet and create a color calculation like
case ATTR( [Measure Name] )
when 'Discount' then ([Discount (Total)] - [Discount (Budget)] )/[Discount (Budget)]
when 'Sales' then ([Sales (Total) ] - [Sales (Budget)] ) / [Sales (Budget)]
See in the attached...
Thank you Michel!
I'm going to take a deep breath (and make a copy of the workbook), and try to move calculations to be built off of a dummy list of the metrics/measure names. I'm holding on to the fact that this will likely help formatting of the whole dashboard too- there are separate sheets for information from Google Analytics, and retention is separate from sales, etc. Getting everything to line up has been less than fun!