10 Replies Latest reply on Jun 27, 2014 9:39 AM by Matt Lutton

# Variance Percent Calculations in crosstab

I finally have a worksheet that is showing Actual Budget and Variance as I need it to.  Now I need to add a variance percentage which is the variance as a percent of the budget - tried various ways with no luck.  I also have Last Year Variance as a (ACtual - LY_Actual) and now I need to show last year % change.  For some reason I can not get these calculations to work.

I then need to color code if variance % is less than last year % change.

Workbook attached.

JUlia

• ###### 1. Re: Variance Percent Calculations in crosstab

SUM([Variance])/SUM([Budget Amount with sign code])

• ###### 2. Re: Variance Percent Calculations in crosstab

Julia - I'm on a mission to do exactly what you have done and are looking to do with the variance %.  Hopefully I will get to the point I can help you but I am brand new at Tableau.   I noticed you created new calculated fields in order to get the variance in absolute dollars.  Why did you have to do this?   You could not simply take the difference between actual and budget?

• ###### 3. Re: Variance Percent Calculations in crosstab

My variance calculation is exactly that the difference between actual and budget. There is a table calculation that calculates the difference but I can not get that to work  the way I was expecting.

I am more a requestor of info than a solution provider but hope my experience with Tableau will grow and I will be able to help answer questions more fully.

Julia

• ###### 4. Re: Variance Percent Calculations in crosstab

Does making your field an aggregate resolve your issue, or are you still looking for help on this Julia?`

• ###### 5. Re: Variance Percent Calculations in crosstab

Matthew / Julia,

I ended up figuring it out - using the Aggregate calculation was in fact the solution.

• ###### 6. Re: Variance Percent Calculations in crosstab

No further help needed - thank you.  Aggregation was the issue.

Julia

• ###### 7. Re: Variance Percent Calculations in crosstab

It would be great if you could mark the response I gave as the correct answer, so as not to confuse others. Cheers

• ###### 8. Re: Variance Percent Calculations in crosstab

Sorry – clearly forum challenged.

The  Finance IS & Reporting team have a Shared Email account for questions and concerns.

Julia Hennelly

Please note I am not in the office on Fridays.

Senior Solutions Analyst, Finance IS Projects & Reporting Operations

3075 Highland Parkway, Downers Grove, IL 60515 web page<http://www.advocatehealth.com/supportcenters>

Phone: 630-929-5528 (Internal: 55-5528)

• ###### 9. Re: Variance Percent Calculations in crosstab

Trying to mark this as correct answer but to no avail.  I simply click on Correct Answer - right???

• ###### 10. Re: Variance Percent Calculations in crosstab

Yes, Julia - I meant marking the appropriate response as the correct answer. The reply you marked was a comment from another user, but the aggregation solution was posted before that in my first reply to your question.

I'm not too worried about it and you can leave it as-is if you like, but it helps others' find solutions in threads quickly and is considered proper forum "etiquette".

Cheers