
1. Re: Actual vs Budget Comparision
Wesley Magee Jun 9, 2018 1:49 PM (in response to Kailash Negi)Kailash,
You could try a layout like this:
This is a dual axis chart. I created a variance calculation that looks like this:
(SUM([Actual])  SUM([Budget])) / SUM([Budget])
I also created a calculation to color the bars whether they are above or below budget that looks like this:
IF [Budget Variance] > 0 THEN "Exceeded Budget"
ELSE "Below Budget"
END
Let me know if any of this doesn't make sense.
Wesley

2. Re: Actual vs Budget Comparision
Bryce Larsen Jun 10, 2018 10:30 AM (in response to Kailash Negi)Hello! Quite similar to the above, I colored each bar based on performance vs budget (outperforming, underperforming, and no budget are blue, orange, and gray, respectively). Budget is shown as a Gantt bar. I chose this over using a reference line due to the simple fact that you can't customize tooltips on the reference line.
I bolded the performance (Actual) and then placed a colored arrow above each bar with the variance %. I looked into doing the variance trend line as you mentioned, but a lot of the time the variance was a negative number, so this made it a bit difficult to graph on top of the bars if doing dual axis.
So, instead I did Actual + Budget as reference line and then Variance as a line graph stacked on top of one another to ensure they stayed separated. Then I removed some of the grid lines accordingly and added banding to group the Measures together:
Maybe this is close to what you wanted? I woudl upload but unfortunately I'm using 10.3.
Best,
Bryce

3. Re: Actual vs Budget Comparision
Kailash Negi Jun 13, 2018 1:57 AM (in response to Wesley Magee)Thanks Wesley Magee
However I want to show it in side by side bar graph and variance as you're showing currently.
Also I want to have colors highlight the variance that is >5% or <5% instead of absolute above or below budget?
Thanks a lot for the help!!

4. Re: Actual vs Budget Comparision
Kailash Negi Jun 13, 2018 2:05 AM (in response to Bryce Larsen)Hi Bryce Larsen
The second one is close to what I need however I would want to show it as a side by side graph and then the variance as a line graph as you've correctly shown. Also can we have colors highlight the variance above and below a certain percentage?
It would be great if you can explain the steps since you can't post the .twbx because of version issue.
Thanks!!

5. Re: Actual vs Budget Comparision
Bryce Larsen Jun 17, 2018 5:35 PM (in response to Kailash Negi)Hello! Sorry for delay. Essentially you just make have two metrics:
 Actual, as already defined in the data
 Variance  a calculated field of: (SUM([Actual])  SUM([Budget])) / SUM([Budget])
You can then use the Variance result to create a field that will be used to color: CF_Variance_Color
Here you can just make a discrete calculated field:
IF ISNULL([Budget]) THEN 0
ELSEIF [Variance] > .05 THEN 1
ELSE 1
END
This will assign all metics/dates that don't have a budget a 0, all that are 5% ahead of budget a 1, and all that are below or up to 5% ahead of budget a 1. You can use these to color.
I then put both of these on the Rows shelf along with the Metric and added some row banding to color and group the Metric calculations together.
Hope this helps!
Bryce
