Budget to Actual - Multiple Dimensions & Different Datasets
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.
Best Answers
-
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.If I solved your problem, please select "yes" above
1 -
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.
If I solved your problem, please select "yes" above
1
Answers
-
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.If I solved your problem, please select "yes" above
1 -
@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?
0 -
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.
If I solved your problem, please select "yes" above
0 -
@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?
0 -
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.
If I solved your problem, please select "yes" above
1 -
Awesome, thanks @ColemenWilson.
I haven't learned of Variables yet so this will be a fun intro.
0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 298 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 616 Datasets
- 2.2K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 729 Beast Mode
- 54 App Studio
- 40 Variables
- 678 Automate
- 173 Apps
- 451 APIs & Domo Developer
- 46 Workflows
- 8 DomoAI
- 34 Predict
- 14 Jupyter Workspaces
- 20 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 121 Manage
- 118 Governance & Security
- Domo Community Gallery
- 32 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive