I've been trying to figure this problem out all day, and I could really use your help.
I have a Calculated Field called "Revenue Pct Change" in a worksheet with a formula like this: "Sum([Revenue])/Lookup(Sum([Revenue]),first())-1"
In addition, I have two measures that give me different results when plotted against my calculated field: "Date" and "Client_type"
When I drag Date in the columns tab and Client_type in the Colors tab, Revenue Pct Change gives me the percent change of revenue for each Client_Type from the given date to the initial date in my dataset depending on the filters.
What I need help with is computing average percent change for each Client_type in a bar graph regardless of Dates. However, my lookup formula in my calculated field now refers to the first Client_type rather than the first Date in my dataset depending on filters. Is there a way to force the Sum formula to only sum across Date instead of whatever it's plotted against? If so, I could take a simple average and be done with it. I could also hardcode this in my data source, but I want to keep the functionality of filters in Tableau.