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"
Let me know if any of this doesn't make sense.
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.
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!!
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.
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
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!