Model Issue: Combining two Fact Tables

[Anonymous]

Hi together,

I have an issue with my report and I guess the reason is within the model architecture.

This is an extract of my model:

I have two dimension tables, Product and Region.
In the first Fact-Table I have a ratio between those tables. So basically a percentage value, how the Product-Share is within the regions. (many-to-many combinations possible)

Based on that I want to filter my Sales data within the FactSales table. (based on the product key out of the first FactTable)

I would like to be able to filter the sales data from both dimensions, depending on the ratio.

Is there another way to do that? I thought of integrating the share directly within the FactSales table. But I need the share also within other fact tables so I want to define it centralized in one spot.

Thank you in advance.

Regards,

Andrears

[Anonymous]

Hi @Anonymous ,

Thank you for your reply, @mickey64 . Here l have another idea in mind. Please check if there is anything that can be improved. Example data can be viewed from the attached .pbix file.Here is my solution:

Method1

1.Merge in the Power Query Editor:

2. Select the two fact tables to create a merged table:

3.Select Sales[$] to expand:

4.The model is shown in the figure:

Method2

1.Create a bridge table:

Bridge Table = SUMMARIZE('FACTProductRegionRatio','FACTProductRegionRatio'[Product Key],'FACTProductRegionRatio'[Region Key])

2.The model is shown in the figure:

Both of these methods can be able to filter the sales data from both dimensions:

Best Regards,
Zhu
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[mickey64 · Accepted solution]

For your reference.

[Anonymous]

This model worked best for my use-case.
Thanks a lot!