Tableau - Calculated fields / grouping / Custom Dim

Question

Tableau:

This may seem simple, but I ran out of the usual tricks I've used in other systems. I want a variance column. Essentially adding a member 'Variance' to the Act/Plan dimension which only contains the members 'Actual' and 'Plan' I've come in where the data structure and reporting is set up like so:

             Actual    | Plan

Profit measure
measure 2
measure 3
etc

The goal is to have a Variance column (calculated and not part of the Actual/Plan dimension)

             Actual    | Plan  |   Variance

Profit measure
measure 2
measure 3
etc

There are solutions where it works for one measure only, and I've looked into that. ie, create calculated field as such

Profit_Actual | Profit_Plan | Variance

You put this on the columns, and you get a grid that I want... except a grid with only 1 measure.

This does not work if I want to run several measures on rows. Essentially the solution above will only display the Profit measure, not Measure 1_Actual , Measure 2_Plan etc.

So I tried a trick where I grouped a the 3 calculated measures, ie Profit_Actual | Profit_Plan | Profit_Variance as 'Profit_Measure' Created a parameter list - 'Actual', 'Plan', 'Variance'

Now I can half achieve my goal, by having the parameter on columns and the 'Profit Measure' on Rows (so I can have Measure 123_group etc down on rows too). Trouble is, I found that parameters are single select only. Only if it can display all options in the custom paramater at once, I would've solved my problem.

Any ideas on how I can achieve the Variance column I want? Virtually adding a member to a dimension/Calculated fieds/tricks/workaround

Thank you Any leads is appreciated

Gemmo

Answer 1

Okay. First thing, I had a really hard time trying to understand how your data is organized, try to be more clear (say how each entry in your database looks like, and not how a specific view in Tableau looks like).

But I think I got it. I guess you have a collection of entries, and each entry has a number of measure fields (profits and etc.) and an Act/Plan field, to identify whether that entry is an actual value or a planned value. Is that correct?

Well, if that's the case, I'm sorry to say you have to calculate a variance field for each dimension. Think about it, how your original dataset is structured. Do you think you can add a single field "Variance" to represent the variance of each measure? Well, you can, store the values in a string, and then collect it back using some string functions, but it's not very practical. The problem is that each entry have many measures, if it had only 1 measure, than 1 single variance field would suffice.

So, if you can re-organize your data, what would be an easier to work set (but with many more entries) is something with the fields: Measure, Value, Actual/Plan. The measure field would have a string to identify what you're measuring in that entry. Value would be a number to represent the actual measure. And the Actual/Plan is the same. For instance:

Measure Value Actual/Plan Profit 100 Actual

So, each line in your current model would become n entries, where n is the number of measures you have right now. So a larger dataset in a way, but easier to work with. Think about, now you can have a calculated field, and use some table calculations to calculate the variance only for that measure and/or Actual/Plan. Just use WINDOW_VAR, and put Measure and/or Actual/Plan in the partition.

Table calculations are awesome, take a look at this to understand it better. http://onlinehelp.tableausoftware.com/current/pro/online/en-us/help.htm#calculations_tablecalculations_understanding_addressing.html

Answer 2

I generally like to have my data staged such that Actual is its own column and Plan is its own column in the data being fed to Tableau. It makes calculations so much easier.

If your data is such that there is a column called "Actual/Plan" and every row is populated with either "Actual" or "Plan" and there is another column called "Value" or "Measure" that is populated with the values, you can force Tableau to make them columns assuming you can't or won't rearrange your data.

Create a calculated field called "Actual" with the following calc:

IF [Actual/Plan] = 'Actual' THEN [Value] END

Similarly, create a calculated field called "Plan" with the following calc:

IF [Actual/Plan] = 'Plan' THEN [Value] END

Now, you can finally create your "Variance" and "Variance %" calculations (respectively):

SUM([Actual]) - SUM([Plan])

[Variance] / SUM([Plan])