Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
@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-...
User | Count |
---|---|
47 | |
27 | |
23 | |
18 | |
15 |
User | Count |
---|---|
55 | |
34 | |
18 | |
17 | |
15 |