Adding Variance to a card

ahunter
ahunter Member
in Charting

Hello! I am struggling to find a solution for an issue I am having. Basically we are creating a table that shows a list of metrics with their Actual Value and Budgeted Value, and I want to add the variance. I have the data formatted in two different ways. The screenshot below shows what the data looks like. If I use the data on the left, I can use a simple beastmode to find variance, if I use the data on the right, I can use a CASE statement beastmode, and this works fine.

image.png

Where the issue comes in, is we have a third metric we are measuring, which is a ratio between the values. We want to calculate 'Revenue/Hour' for the actual amount and budgeted amount, as well as the variance between the two. For this I know how to make the beastmodes to get the ratios themselves using either set of data, but I cannot figure out a way to show the variance between the budgeted and actual ratio.

I know I could just add the ratios in the ETL, but I don't want the ratios to be static based on a timeframe. I would like to be able to create a card where we can change the timeframe and the ratios will change dynamically. Does anyone know if this sort of thing is possible? I am also fine if the calculated ratios would have to be in a separate card from the already listed metrics. I know it could be hard to blend the two in a single card. This is the type of format that I want the output to look like:

image.png

Best Answer

  • MarkSnodgrass
    MarkSnodgrass Coach
    Answer ✓

    I think you are going to want to pivot your data further in your ETL so that you have one row per date with the following columns: Actual Hours, Budget Hours, Actual Revenue, Budget Revenue, Date.

    This will allow you to do the math for the ratio very easily.

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

Answers

  • MarkSnodgrass
    MarkSnodgrass Coach
    Answer ✓

    I think you are going to want to pivot your data further in your ETL so that you have one row per date with the following columns: Actual Hours, Budget Hours, Actual Revenue, Budget Revenue, Date.

    This will allow you to do the math for the ratio very easily.

    **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.
  • ahunter
    ahunter Member

    Hello Mark! Thank you for your response, that makes sense. The only thing is that our actual dataset is much larger and has like 100 metrics, so this would end up being a ton of columns. Do you know if there would be any way to simplify it?

Categories