1 of 1 people found this helpful
In the case of a display like this, which I would call an "inventory" of sorts, I would recommend a visual closer to your "current state" than what you've identified as your target.
AREA chart for the "beginning balance" or "ending balance" for the period in question (I prefer ending balance, but you appear to have the [Members at Start of Period] as your "inventory" figure)
Positive Bars for additions
Negative Bards for subtractions/removals.
But, if you want to, you could create the visual you had in your screenshot too.
The key is using Measure Names on the Columns shelf.
However, because your data is structured as a single record per period, you are using different Measures for "Additions" and "Subtractions."
The key to setting up your data to lend itself to your desired visual is that you want to UNPIVOT your source data so that you have a single column with an amount and then a "description" field for the metric type.
This will reduce the columns and increase the rows in your data, but grants you greater flexibility in Tableau.
Forgive my crappy screenshot--I don't have Excel installed on this computer, so I had to use Google Sheets.
Hopefully that gives you the idea.
With your data shaped that way, you can now have greater flexibility.
I'd create an additional calculated field as
"Inventory" which basically "undoes" the unpivot and pivots the inventory figures back out into their own field:
IF [Metric Type] = 'Inventory' THEN [Count At Start] ELSE NULL END
Then create another Calculated Field (since your raw data doesn't handle the sign of the "Deleted" items--I'll call it "Added/Deleted":
IF [Metric Type] = 'Added' THEN [Count At Start]
ELSEIF [Metric Type] = 'Deleted' THEN -1*[Count At Start]
Then, you can Measure Values on the Rows Shelf and Measure Names on the columns shelf and get 2 "columns" where the first will be Inventory and the second will be the signed [Added/Deleted].
You can color by 'Metric Type' to get 3 distinct colors.
I agree with Mark regarding placing all the measures in a single column.
I did this with the Pivot function on the Data Source Tab.
As Mark described, this allows for calculations to be done using the Pivot Field Name,
so you could set a position 0 for the Start Value and Position 1 for the other values.
Please see workbook attached in the Forum Thread.
268065three.twbx 31.6 KB