Budget vs Actual visual using only measures (e.g, variance chart, or tornado chart)

[omillzy]

All of our budgets/targets are stored seperately to our source data in spreadsheets. In addition the budgets are at a higher level of granularity to source data (budgets by month, region, product).

I have a bunch of measures like this:
# Leads (Actual)
# Leads (Budget)
# Opportunities (Actual)
# Opportunities (Budget)
and so, for each stage of our sales pipeline...

Essentially, I want to be able to compare either variance between the 2, or skewness of the funnel (a skew to the right would show we are above budget, a skew to the left would mean we are below for example).

I have seen the tornado chart work well for this, except this requires a grouping variable (e.g, sales stage) and does not work for measures. I have also seen xViz has a variance chart which just shows + or - difference between the two figures, however this also relies on a grouping category.

The only solution I can think of is aggregating my source data up to the same level of granularity as the budgets, union the two datasets and creating a column called 'datasource' (with Budget and Actual as values), however, I want to avoid doing as it will require a lot of manipulation and becomes troublesome when users want to see the underlying data points for the actual figures.

Are there any custom visuals out there that I can just drag and drop my measures on and create a tornado chart, or variance chart?

[dedelman_clng]

Hi @omillzy  - is this the look you're going for? Simply a horizontal bar chart showing the variances (it's actually two chart, I just removed the Y-axis from the right-most chart)

Hope this helps

David

[omillzy]

Basically, yes - I know the standard bar chart can do this but I couldn't figure out how to conditionally format the colours to neg was red and pos is green, is this a custom visual or standard bar chart? 

[dedelman_clng · Accepted solution]

Hi @omillzy  - 

This is the standard bar chart. I've done what you're trying to do by creating Positive and Negative measures for each measure I want shown. Basically

BudgVarNeg = IF([AllSpend] <> 0, IF([BudgVar] > 0, [BudgVar]))

BudgVarPos = IF([AllSpend] <> 0, IF([BudgVar] <= 0, [BudgVar]))

Then used a stacked bar chart with both of those measures as values, assigning Neg to Red and Pos to Green (don't mind the line - it's not related to what you're asking, and I know this is a column chart but the principle should be the same for a horizontal bar chart).

Hope this helps

David