Budgeting and Product Granularity

Power BI Data Model
1

I like to make my reports and data models as simple as possible. This is to ensure the users follow the path you have created and dont stray.

My model is basically four tables

  • Product - this is at product level and includes the product category (parent of product).
  • Sales - by Month and product
  • Budget - by Month and Product Category
  • Calendar - by Month

When I create my data model I would like to see
Fact Sales,
Fact Budget
Dim Product
Dim Calendar

I also want to join Product to both the sales and budget tables!
This is the underlying data
model|602x500

This is what the customers want

Data Model
Data Model819×517 6.25 KB

What I really want is One product dimension that joins to Sales and Budget at different levels. Is this possible

Target
Target819×517 6.1 KB

Thanks
E

2

Hi @ells. Have you modelled your sample data in a PBIX? If so, please attach. Also, do you have an Excel mock-up of a possible report (desired outcome) you think the users may want; may help to describe the question/issue.
Greg
_eDNA Forum - Format DAX

3

Will knock up a quick one.
Thanks
E

4

@Greg,
Many thanks. PBIX attached. The first draft is where we have two star schemas and when it comes to Sales we have this connecting to two dimensions - ProductCategory and Product.

I want to use just one dimension. This means when it comes to analysing sales and Budget there will be one product dimension not two.

Thanks
E

5

I know there are many ways to achieve this but for me it is best practice and simplicity.

Thanks

6

@Greg
Going to add an updated pbix as there were some issues in that one

7

@Greg
attached an updated PBIX. It does not seem to be working as expected. It is enough top give you an idea. The key for me is to getting showing a single Product dimension and to be able to join to Budgets at category level and to Sales at Product level.
Thanks
E

8

@ells,

I would say that not only is it possible, but that it would be the ideal way to model it. (however, in the second diagram, your arrows should be going in the opposite direction). In a time-based model, you would typically allocate your budget table info to match the higher granularity in your fact table. However, in this case, I think might aggregate your more granular fact table data to match the granularity of your budget table, unless you have a good model for allocating product category data to the individual product level.

  • Brian
9

Budget.pbix (33.8 KB)

@BrianJ @Greg
I think I have a solution now. However as there is always a price it comes at a many to many relationship. I have one Product dimension and it joins Sales at Product id ;level (1 Product: m Sales) and then it joins to the Budget table at Product Category level(m:n Product Category: Budget)

This will give the users what they need - a simple way to compare.
Thanks
E

10

@ells,

Personally, I would avoid the M:M, and just do this as a virtual relationship using TREATAS. But, if the current model works for you and gives your users what they need, go for it.

  • Brian
2 Likes
11

Hi @ells, did the response provided by @BrianJ help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!

12

Hi @ells, we’ve noticed that no response has been received from you since the 22nd of January. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!

13

The current solution will work. It may not be the best solution and I have seen a you tube by Alberto Ferrari

This shows several methods and the intermediate table solution may be better once I see the users using the report.
Many Thanks
E