Budget by two different attributes of a single table

[sgeheeb]

Hi, I am having the following issue:

I need to implement a budget analysis in Power BI and I am at a bit of a loss on how to do it in this case.

The data is just for demonstration purposes and does not add up.

I have a fact table:

Amount	Date	Customer Key
100	2023-01-01	1
200	2023-01-01	2
200	2023-02-01	1

And a Customer Dimension:

Customer No	Customer Group	Customer Sales Rep	Customer Key
C1	GROUP 1	SR 1	1
C2	GROUP 1	SR 2	2
C3	GROUP 2	SR 1	3
C4	GROUP 3	SR 1	4

So far there is no problem. I could create a single level budget (by Group or Sales Rep), or a multi-level budget (e.g. Group -> Customer, Sales Rep -> Customer, Group -> Sales Rep -> Customer) and even add in the date component.

However, the result, I am expected to deliver should look like this in a Matrix:

Customer		Sales	Budget	Diff
GROUP 1		1000	1200	-200
	SR 1	500	500	0
	SR 2	500	700	-200
GROUP 2		500	400	100
	SR 1	200	100	100
	SR 2	300	300	0
GROUP 3		1000	800	200
	C 1	500	400	100
	C 3	200	100	100
	C 4	300	300	0
Total		2500	2400	100

The top level of the hierarchy is always the Group

The second level of the hierarchy is sometimes the Sales Rep, sometimes a Customer, so basically, some Groups are budgeted by Customer, some Groups are budgeted by Sales Reps.

I need to come up with a structure for a budget file, into which a someone can enter their budget data. I will then import the budget file into Power BI, connect it to the customer dimension and thus be able to do the comparisons.

I am at a bit of a loss on how to do this. I have dealt with ragged hierarchies before, but since this is strictly a two-level hierarchy without empty leaves, etc. I'm not sure if this even qualifies as such.

Can it even be done like this? Do I need to create two seperate budgets? Or am I missing something obvious?

Thanks!