row total is not correct

msharma
msharma Member
in Charting

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.

image.png


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

image.png

Thank you,

Monika

Answers

  • jaeW_at_Onyx
    jaeW_at_Onyx Coach

    @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.

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • MarkSnodgrass
    MarkSnodgrass Coach

    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.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.

Categories