I am in the process of transition an Excel spreadsheet (with SQL as data source) into Tableau. I am running into issues with referencing dimension subtotal for YoY calculations.
This is what I am trying to create:
However, when I tried to replicate this in Tableau, I am unable to re-create Weekly Total as a Calculated Field because Market is a dimension and not a series of measures. While I could get that Weekly Total by using the "Show Total" option under Analysis dropdown, I don't believe I can reference that subtotal values.
This is what my Tableau worksheet looks like:
Any thoughts on how I can work about this so I can display the Weekly Total of this year and last year to perform the YoY calculations? Another workaround that I've thought of is using case statements in the SQL query itself to convert each city into a measure but that doesn't seem like the most efficient idea either.