Adding Variance to a card

ahunter

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.

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:

MarkSnodgrass

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.

MarkSnodgrass

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.

ahunter

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?