Budget to Actual - Multiple Dimensions & Different Datasets

Data_Devon · 2024-06-20T16:09:33+00:00

There was an error rendering this rich post.

I have a budget dataset with the following columns:

_Account
_Subsidiaries
_Department
_Doctor
_Location (Not in Use)
_Headcount Resources
_Placeholder 4
_Year
_Period
_Scenario
_Currency
_Measure
_value

I have an actuals dataset with the following columns:

Appointment Date
Status
Appointment Type
Patient Account Number
Location
Provider

I want to be able to overlay budget and compare it to actuals. This seems simple enough in theory, but the differing datasets is causing difficulties:

The end goal at the highest level is to see how many appointments we have scheduled and how many we have budgeted within the same simple view. I.e. We have 100 surgeries scheduled but 200 budgeted.
The actuals dataset is obviously at a day level. (I.e. June 20), where the Budget is at a monthly level (I.e. June Month)
The actuals dataset is at a singular level (i.e. 1 surgery), where the budget is grouped/pivoted to the time frame (i.e. 500 surgeries (for Month of June)).
There are multiple Providers, multiple Locations, multiple Appointment Types, Multiple Statuses, etc….. It's not as simple as a 1:1 join.
Finally, the datasets have unneeded information. The actuals dataset includes "Status" data of "Cancelled", which doesn't need to be included since it doesn't actually exist and didn't happen. The budget dataset includes different "Measures" that affect the "Values". If the Measure is "Units" then the Values is interpreted as surgeries. If the Measure is "Value" then the Values is interpreted as cash revenue.

I attempted to solve this with a separate question (see link below), but didn't provide enough context to allow for more tailored assistance.

Cheers to anyone that can help tackle this - this is a huge initiative that would be a big win for the team.

https://community-forums.domo.com/main/discussion/67184/compare-to-budget-actuals-in-one-dataset-and-budget-values-in-second-dataset

Find more posts tagged with

Dataset

Join

Magic ETL

Beast Mode

Data Flows

Accepted answers

ColemenWilson

Append!

Use Magic ETL to:
1. Match column names and data types where appropriate (like date)
2. Get both datasets in the same granularity with same aggregation
3. Remove unneeded columns
4. Append the data together rather than join.

ColemenWilson

My next steps would be to calculate the variance between budget and actual - this would be your y axis and then choose how you want to view it - by location, by account, by doctor, by date - whichever you pick would be your x-axis. If you want to view by each of these you could create a variable and the end user could toggle between the different views. https://domo-support.domo.com/s/article/7903767835031?language=en_US

All comments

ColemenWilson

Data_Devon

@ColemenWilson Thanks for the response. Excited to make this happen.

I've been assigned with creating something like this (see screenshot below). How will appending allow for comparison of the same time periods?

ColemenWilson

Looks like you'll create a stacked bar chart of some sort. If you provide more information and some sample data I could direct you more.

Data_Devon

@ColemenWilson Thanks a ton! That was a very simple solution, so thanks for helping out.

I now have all of our actuals and our budget appended into one gigantic dataset (the hard part).

Now, how can I begin to compare budget to actuals with all these rows? I added a constant before the append, so that each row is tagged with either "Budget" or "Actual". We have multiple locations, multiple accounts, and multiple doctors, so I'm not sure how to visualize this now.

Any ideas?

ColemenWilson

Data_Devon

Awesome, thanks @ColemenWilson.

I haven't learned of Variables yet so this will be a fun intro.