Compare to Budget - Actuals in one Dataset and Budget Values in second Dataset

Data_Devon

Hi community,

BLUF: How can I compare budget to actual?

We have data in one system that holds our actual sales amounts. We have data in a separate system that holds the budgeted sales amounts. I want to show the actuals and the budgeted in the same card.

This seems easy enough in theory, but I've been struggling to implement it. The biggest issue, of course, is that the two systems speak different languages. The Actual dataset has the location titled as "Location", where the Budget dataset has the location titled as "Subsidiary". Other examples of discrepancies: LastName, Last. Account, Stats Account.

If the column name was the only issue, then it'd be simple enough. But unfortunately the underlying data is different too. For instance, in Actual, the Location is listed as "Sioux Falls", but in Budget, the Location is listed as "100 Sioux Falls"

There isn't one column that's common across the two datasets, so I don't know how to join them. If I do ETLs to CASE WHEN one dataset into the other's language, that seems overly cumbersome for what seems to be a simple ask.

I've tried everything I can think of. I would love to see a bar that has the "budgeted" amount, and then a bar that has the "actual" amount.

Any help is appreciated.

david_cunningham

@Data_Dev, I'm suggesting the opposite. If you take your monthly budget values, and turn them into daily budget values (by either dividing by the number of days in the month, or aggregating up to the year and dividing by the number of days in the year), you will end up with daily budget and daily actuals. This will let you do things like, MTD actual vs budget, last 30 days actual vs budget, etc. It will also let you filter to any date range, and have your actual vs budget be accurate. See here for a reference.

https://community-forums.domo.com/main/discussion/comment/95553#Comment_95553

If you go through and map all the values, I would recommend doing this in a new column rather than updating the values in the existing column. This will let you display the data using the non-edited values, but still join.

david_cunningham

You'll need to do some mapping to create a way to join. I would also personally recommend aggregating up to a daily budget/actual amount in your ETL. You would then need to join to a date dimension table to fill in data for days where there were no "Actual" sales, but there was a budget. Together this will enable you to create a dynamic and filterable budget vs actual for any time period.

Data_Devon

Thanks David_Cunningham for a quick response!

So, to clarify, I'll need to remap all of the actual columns so that they match exactly how Budget columns are reported. This is what I was afraid of 😂

"Please expand upon your aggregating up to a daily budget/actual" comment. Our actuals are on a daily basis and our budget is on a monthly basis. Are you suggesting I combine all actuals to aggregate them up to a Monthly basis?

david_cunningham

@Data_Dev, I'm suggesting the opposite. If you take your monthly budget values, and turn them into daily budget values (by either dividing by the number of days in the month, or aggregating up to the year and dividing by the number of days in the year), you will end up with daily budget and daily actuals. This will let you do things like, MTD actual vs budget, last 30 days actual vs budget, etc. It will also let you filter to any date range, and have your actual vs budget be accurate. See here for a reference.

https://community-forums.domo.com/main/discussion/comment/95553#Comment_95553

If you go through and map all the values, I would recommend doing this in a new column rather than updating the values in the existing column. This will let you display the data using the non-edited values, but still join.

Data_Devon

That makes sense @david_cunningham. Thank you for your thorough explanation.

I see the quote of what must of been a previous question and response, but I don't see a link to the original post that your answer responded to. Perhaps that isn't important but wanted to be sure I wasn't missing something.

Thank you David!!

david_cunningham

@Data_Dev - here is a link to the entire post.

https://community-forums.domo.com/main/discussion/66867/etl-how-to-spread-a-budget-over-two-dates-within-a-variable-date-range

Data_Devon

Resurrecting this….

@david_cunningham I still don't fully understand how to Cartesian Join on the date dimension table. I oversimplified our dataset, so the screenshots you provided couldn't translate directly to our data.

I want to join these two datasets to see how many appointments we have scheduled compared to how many we budgeted for the same time frame.

The difficulty increases because we have so many extra variables: different locations, different employees, different TYPES of appointments, etc.

I'm at a loss, but I still think this is possible. Let me know any thoughts!