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.
3 of 3 people found this helpful
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!