
1. Re: Variance to baseline
Rohit Yeruva Feb 14, 2020 10:02 PM (in response to Alpha Beta)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

P&L.twbx 586.9 KB


2. Re: Variance to baseline
Alpha Beta Feb 15, 2020 4:33 AM (in response to Rohit Yeruva)Hi Rohit,
Thank you for your reply.
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
Zhouyi Zhang Feb 15, 2020 5:36 AM (in response to Alpha Beta)
P&L_v2018.1.twbx 549.1 KB


4. Re: Variance to baseline
Rohit Yeruva Feb 15, 2020 6:57 AM (in response to Alpha Beta)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 companyitem 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
Alpha Beta Feb 17, 2020 2:41 AM (in response to Alpha Beta)Just one followup 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
Zhouyi Zhang Feb 17, 2020 4:41 AM (in response to Alpha Beta)1 of 1 people found this helpful 
7. Re: Variance to baseline
Alpha Beta Feb 17, 2020 7:18 AM (in response to Zhouyi Zhang)Yes, thank you!