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.
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.
How should I start?