cancel
Showing results for 
Search instead for 
Did you mean: 
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

@JayJay25 , refer if these two blogs can help

 

Power BI Distributing/Allocating the Monthly Target(Convert to Daily Target): Measure ( Daily/MTD): https://community.powerbi.com/t5/Community-Blog/Power-BI-Distributing-Allocating-the-Monthly-Target-...

 

https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-o...



Learn Power BI - Full Course with Dec-2022, with new DAX functions like Window, Index, Offset !!
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Formatted Profit and Loss Statement with empty lines

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors