Shape of the data looks ideal to me.
You could create the following calculations and plot those using measure names and measure values independently.
Calc 1: Actual 2018 (or current year)
Calc 2: Actual 2017 (or last year)
Calc 3: YoY absolute
Calc 4: YoY %
Calc 5: Budget 2018 (or current year)
Calc 6: Budget v. Actual 2018
Thanks for the input.
I've tried your method and I've got exactly what it's meant to be, as the below:
However, what if I want this table to have an extra row for Sales GP% (which is basically [Sales] - [Cost of Sales])?
For Sales GP%, I will have Actual 2018 value, Actual 2017 value and also, difference versus last year. Is this even possible?
And my second question is, hopefully you can help me out here, if I store data as below, does it help to improve performance and ease of calculations?
Again, I've attached my workbook here for your reference.
data.twbx 29.3 KB
For the GP field, you could create a new Amount field that flips the sign from normal (positive) to negative for expenses. You could then sum up this new 'sign-adjusted' Amount field across all the accounts to derive your GP amount.
If you added an account type of Revenue, Expenses etc, you could also add relevant subtotals.
I do not know how you could add the GP% within the same view, but you could perhaps set it up on another sheet and then incorporate alongside the absolute figures within a dashboard.
If the data is wide (your last screenshot) you can create calculated fields for GP and GP%. You may find it harder to change the structure later though.
Finally, here are some resources that may be of interest:
A reference sheet from Tableau structured around a relevant training course
A useful explanation of drill down in Tableau
Andy Kriebel's beautiful way to visualise some P&L elements on a dashboard