Forum Discussion
Beginner help with finding the difference between two tables
I have two tables:
An annual budget, organized by month and category.
| Month | Category | Amount |
| January | Hardware Operating Expenses | $5,000 |
| February | Hardware Operating Expenses | $5,000 |
| March | Hardware Operating Expenses | $5,000 |
| ... | ||
| January | Software Licenses | $7,000 |
| February | Software Licenses | $7,000 |
| March | Software Licenses | $7,000 |
| ... |
Another is a seperate table that contains transactions records for actual spend (Date, Merchant, Sub-category, Amount).
- This transactions table contains records from this year, and several years back
- There is a custom column in this table that determines which sub-categories belong to which top-level category
Top Level Category =
SWITCH(
TRUE(),
transaction[sub-category] = "Computers", "Hardware Operating Expenses",
transaction[sub-category] = "Consumables", "Hardware Operating Expenses",
transaction[sub-category] = "Microsoft 365", "Software Operating Expenses",
...
)
I am trying to find a way to analyze the difference between budgeted spend vs. actual spend, based on the category. My hope is that I can display a table that shows my original budget vs. actuals (+/-).
Looking for help on starting points.
Thank you.
Hi RD6308 ,
Since you did not give a specific table, I had to create my own table for testing according to your description, please point out if there are any problems.
Please try below steps:
1.below is my test table
Budget: "Index" column are used to sort the "Month" column by this column
Transcations:
Model:
2. create a measure and add it to table visual
Budget VS Actuals = VAR cur_month = SELECTEDVALUE ( Budget[Month] ) VAR cur_budget = CALCULATE ( MAX ( Budget[Amount] ), Budget[Month] = cur_month ) VAR cur_actuals = CALCULATE ( MAX ( Transcations[Amount] ), Transcations[Date] = cur_month ) VAR diff = cur_actuals - cur_budget RETURN IF ( diff > 0, "+" & diff, "-" & diff )Please refer attached .pbix file
Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Hi RD6308 !
Are you familiar with the star schema in data modelling?
You will need to create a dim table for 'Date' and 'Category', and connect the dim tables to the fact tables 'Actuals' and 'Budget' with a one to many relationship.
Visual aid:
Once you have created your datamodel you can create the measures and visuals.
Get the values from the Fact tables like:
Actuals = SUM('FactActuals'[Value])
Budget= SUM('FactBudget'[Value])
Difference = [Actuals]-[Budget]Use the dim tables as your lookup values.
e.g. Matrix: Row = Category from dimCategory ; Header = Year from DimDate ; Values = Explicit Measures from fact tbl
Hope it helps!
Kind regards,
OD
Thank you. I have not been able to figure out realtionships previously. With the creation of DIM tables for 'Date' and 'Category', I am now able to create a One-to-Many relationship successfully.
I'll continue work on it from here and see how far I can get.
One issue I ran into -- it took Date Hierarchy away from my Actuals table. Dates now show up as (what I guess to be) plan text (ex. "Saturday, August 13, 2022"). Did I do something wrong?
Hi RD6308 ,
Since you did not give a specific table, I had to create my own table for testing according to your description, please point out if there are any problems.
Please try below steps:
1.below is my test table
Budget: "Index" column are used to sort the "Month" column by this column
Transcations:
Model:
2. create a measure and add it to table visual
Budget VS Actuals = VAR cur_month = SELECTEDVALUE ( Budget[Month] ) VAR cur_budget = CALCULATE ( MAX ( Budget[Amount] ), Budget[Month] = cur_month ) VAR cur_actuals = CALCULATE ( MAX ( Transcations[Amount] ), Transcations[Date] = cur_month ) VAR diff = cur_actuals - cur_budget RETURN IF ( diff > 0, "+" & diff, "-" & diff )Please refer attached .pbix file
Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
