Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
JayJay25
New Member

Budget Allocations by category but based on different granularity

I am trying to work on a personal finance report and want to incorporate monthly budgets I set for myself.

 

I have created the data model as the following:

Transactions Table = Export from Bank transactions outlining day to day transactions with the vendor name

Category Mapping = Table that maps Names of vendors to a set amount of category

Budget = A set of categories, their budgeted number (monthly)

 

I have a one to many relationship on Vendor with Transactions and Category Mapping and a One to many relationship with Budget and Category mappings on the Category name.

 

So the dataset looks something like this:
Transactions:

Date | Vendor | Amount

Day x | Vendor x | Amount

Day x | Vendor z | Amount

Day y | Vendor y | Amount

Day z | Vendor B | amount

 

Category Mapping

Category Name | Vendor

Category Name 1 | Vendor x

Category Name 2 | Vendor B

Category Name 1 | Vendor y

Category Name 3 | Vendor z

 

Budget

Category Name | Budgeted
Category Name 1 | 100 (budget)
Category Name 2 | 200 (budget)
Category Name 3 | 100 (budget)

 

I am having a really hard time coming up with a measure that would show me the difference in the amount spent vs amount budgeted on a monthly basis. I know the data is on different granularities as the data in transaction is daily (and can have multiple days) vs data in the budget is just a single monthly value by category..and I just cant figure out how to relate the two.

 

So far this measure would show me the amount of spend by categories:

SpendByCategory = SUMX(VALUES('Category Mappings'[Category]),CALCULATE(SUM('Bank Export'[Amount])))
 
But how do i lookup the category and pull the amount that I need to subtract from the actual spend ?

1 REPLY 1
amitchandak
Super User
Super User

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors