7 Replies Latest reply on Feb 17, 2020 7:18 AM by Alpha Beta

# Variance to baseline

I would like to compare providers line items (sales, CoGS, gross profit, EBITDA, operating profit) across providers A, B, C, D, and F to provider E, which serves as my baseline. I'd like to know the percentage difference regardless of year or whether or not I have companies in the view. Both sheet 1 and sheet 2 should show the variance.

The result should look something like this:

ProviderSalesCoGSEBITDA
A10%5%20%
B2%0%-3%
C1%-1%4%
D-6%-2%0%
E
F2%3%4%

I'm not sure how to do this with table calculations. The "relative to" option does not let me select a specific row.

I'm using Tableau 2018.1

Thank you.

• ###### 1. Re: Variance to baseline

Hey AB,

Just create a field which has your baseline values only and use that to compute your variances.

1. New field which represents the baseline (provider E):

2. Variance calculation: You can adjust this as you like. I compared the avg values for each provider against the base:

3. Use the variance that you calculated in your table:

Let me know if this is what you need. Also attached a workbook for your reference.

//Rohit

• ###### 2. Re: Variance to baseline

Hi Rohit,

Your solution works really well unless I select multiple years in my filter at once. I'd like to look at 2015, 2016, 2017 and 2018 at the same time and suddenly my variance for provider E (which should be 0) is 382,501 for revenue. Do you know why that might be?

• ###### 3. Re: Variance to baseline

Hi, there

Please find my solution attached as well as below screenshot of steps

HOpe this helps

ZZ

• ###### 4. Re: Variance to baseline

Hey AB,

I see! That seems to be because for a certain years (E.g., 2016, 2017), there seems to be more than 1 record for a company-item combination. So, just tweak the "Baseline: E" calculated field as below (as suggested by Zhang) and it should work:

{EXCLUDE [Provider]: AVG(IF [Provider] = 'E' THEN [Value] END)}

//Rohit

• ###### 5. Re: Variance to baseline

Just one follow-up question: How would I calculate the percentage difference? I tried the formula below, but my results are somewhat off:

100*(SUM([Value])-AVG({ EXCLUDE [Provider]:SUM(IF [Provider] = 'E' THEN [Value] END)}))

/

((SUM([Value])+AVG({ EXCLUDE [Provider]:SUM(IF [Provider] = 'E' THEN [Value] END)}))/6)

Thank you

• ###### 6. Re: Variance to baseline

Hi, there

Is it something like this?

ZZ

1 of 1 people found this helpful
• ###### 7. Re: Variance to baseline

Yes, thank you!