Data Model in case of possible groups in fact table

[kmsc]

Hi,

I'm struggling with specific data structure and I will appreciate any advices regarding data model design in this kind of case.

The 'fact table' for me is a dataset with customer's agreements and prices for different products. It's tricky for me due to possible of "group of products" and "group of customers". Prices can be established for single / group or all customers / products. We could have agreement with base price for group of clients and group of products etc.

I have created similar example of this case in Power BI.

In result, I want to have possibility to filter customers / products (specific, not a group) and view details regarding agreed prices. I was trying to add dimension table with unique customers / products, but I'm not able to receive expected result.

It's more complicated, because unique product can be in different "groups of products" due to different market (combination of products&market should be unique).

I guess that dimension table with products / customers and markets is mandatory, but I'm not sure how relationships should look like. I was also thinking about merging tables Prices & Products to extend 1 row group_products - prices to multiple rows (all products and the same price for each).

Thanks in advance for any suggestions.

PBIX file 

[kmsc]

Any ideas, thoughts? 🙂 Do I explained it in wrong way or should I provide any new details?

I would appreciate any reply. Thanks in advance.

[v-henryk-mstf]

Hi @kmsc ,

First of all I am not able to open the test file you provided and can share it in a different way.

Also can you provide a screenshot of the expected result and describe it so that I can answer it for you as soon as possible. Looking forward to your reply.

Best Regards,
Henry

[kmsc]

Hi,

Thanks for quick reply. Let's try using my google drive:     File 

Regarding expected result:

In result, I want to have possibility to view agreed prices for any chosen product / customer in selected market (even if product belongs to defined group of products, which has agreed price in "Prices" table). It would be very easy, but possibility to agreed price for defined group of customers / products making this case harder for me and I didn't find yet optimal and working as expected data model.