row total is not correct

msharma

I am working to create a Pivot table, where Year_Budget column is the average amt and Actual Amt is the the aggregrate.

There are two questions, the subtotal of Year_Budget is not correct, it should be 6195907.00, however is it displayed as 476608.23.

Also when I try to create a difference in value in BeastMode, I get a syntax error:

Thank you,

Monika

jaeW_at_Onyx

@msharma

if i had to bet, i'm putting money on the fact that you JOINED the budget onto the Transaction Fact, and that's why you're using the Average.

Don't.

Use a UNION to append the budget rows to your dataset. That way you can avoid having to use Avg.

See my tutorial video:

https://www.youtube.com/watch?v=PVbOeLSae9o&t=60s

Re, your beast mode, remove the = sign. Use MySQL syntax.

Re, analyzer giving the 'wrong math', challenge your assumption. Why do you think the average should be <your number>?

If the annual budget for that GL Account is Joined to each transaction as I suspect, then you can't assume that your outcome will be a neat summation of the AVGs. That's not how AVG works. AVG would be the average of the amount across all transaction rows OR the avg, of the aggregated amount for each GL Account_Description. // either way, using average will not yield consistent or desired results.

MarkSnodgrass

Your beast mode is giving an error because you have an equals sign at the beginning of it. Remove that and it should return as valid.