Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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!
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
Check out the May 2024 Power BI update to learn about new features.
User | Count |
---|---|
90 | |
80 | |
60 | |
60 | |
58 |
User | Count |
---|---|
155 | |
119 | |
104 | |
78 | |
71 |