Display the variance between two variables on multi-line graphs

user08235
user08235 Member
in Charting

How can we get the data labels to show the variance between two variables on either multi-line or running-total line graphs? It looks like the only options are to display the actual value of the variable, but not relatively to the other variables.

 

Best Answer

  • Jaketh13
    Jaketh13 Contributor
    Answer ✓

    @user08235 Definitely. If you already have them broken out by columns, and your aggregating the other two, then you should be able to create a beast mode that calculates the variance and put it in as another series. So, you'd want to leave the `Sales` and `Budget` columns in the Y-axis and the first series. Make sure they're summed. Then you can create a beast mode that does something like:

    SUM(`Sales`) - SUM(`Budget`)

     

    Depending on how you're data is structured, that should do the trick.


    Thanks,
    Jake
    **Say "Thanks" by clicking the "heart" in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"

Answers

  • nicolasfeddern
    nicolasfeddern Member

    One option might be to use the period over period chart types if you have a date dimension in your dataset. This will give you the variance by default without needing to create a calculated field. Otherwise, you would need to create a new field with the variance calculation you're wanting to display, such as the percent change: (Current Value - Previous Value)/(Previous Value) or (Current / Previous)-1. 

     

    At present, there are no default card options for displaying deltas or variances in a data label without creating a calculated field.

  • Jaketh13
    Jaketh13 Contributor

    Hey @user08235,

     

    @nicolasfeddern's answer is definitely the easiest. If for some reason that doesn't work for your data, you could also create a DataFlow. Once it's in the card, you can graph your column and break it out by series, but all the values need to be in that column. You can create a DataFlow where you pivot out your series by date period you are measuring (to get your two graphed values on the same row) then add a new column that is the variance between the two new columns you've created. From there, you can append your new column onto the original column and break it out into 3 series: First value, second value, and variance.

     

    Let me know if you'd like more in depth explination.


    Thanks,
    Jake
    **Say "Thanks" by clicking the "heart" in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • user08235
    user08235 Member

    @Jaketh13 So it actually sounds like I may have my data structured in the way you described (if i'm understanding correctly) because my data is structured by having MONTH as the row, and the 'SALES' and 'BUDGET' values stored in columns (see attachment for visual). In order to create a 'variance' column (variable) and plot all 3 as lines on the same graph (as you suggest), can I do that just using a beastmode calculation that would calculate the:

    1. SUM(SALES of X month) 

    2. SUM(BUDGET of X month)

    3. VARIANCE between sum sales and sum budget

    *(I don't know what the right formula would be to do that)

     

    Or would I still have to accomplish this using a dataflow?

     

    Let me know if i've completely misunderstood your suggestion, in that case I may need the extra detailed explanation that you offered ? Thanks!

  • Jaketh13
    Jaketh13 Contributor
    Answer ✓

    @user08235 Definitely. If you already have them broken out by columns, and your aggregating the other two, then you should be able to create a beast mode that calculates the variance and put it in as another series. So, you'd want to leave the `Sales` and `Budget` columns in the Y-axis and the first series. Make sure they're summed. Then you can create a beast mode that does something like:

    SUM(`Sales`) - SUM(`Budget`)

     

    Depending on how you're data is structured, that should do the trick.


    Thanks,
    Jake
    **Say "Thanks" by clicking the "heart" in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • user08235
    user08235 Member

    Yes - perfect! I was able to create the variance as it's own field and show all 3 variables in a single graph. Thanks!

  • user08235
    user08235 Member

    @Jaketh13 One last thing - is it possible to change the style of a line for 1 of the 3 series? For example, make it a dashed line, while the other 2 variables remain sold? This would make it easier for a user to visually see the difference between the 'variance' variable versus the 'sales' and 'budget' variables. Thanks again for your help!

  • Jaketh13
    Jaketh13 Contributor

    @user08235, Unfortunately, not yet. The only suggestion I would have is to make it a drastically different color than the other lines using color rules.

     

    That said, it sound like a great idea. For things like this, you can check out our Ideas exchange here https://dojo.domo.com/t5/Ideas-Exchange/idb-p/Ideas. If your idea exists then vote it up, otherwise submit your idea so others can vote it up


    Thanks,
    Jake
    **Say "Thanks" by clicking the "heart" in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
This discussion has been closed.

Categories