Data modeling for a relationship between products and city

[NewUser3300]

Hello there, I´ve been struggling with this issue.

I have a table of products with a base price, but the price changes based on the city its sold (distribution fee). 

Id	Product	Price
1	Product 1	20.00
2	Product 2	30.00
3	Product 3	25.00

 And I have another table like this.

Id	City	Product	Distribution Fee
1	New york	Product 1	1.2
2	Chicago	Product 1	1.3
3	Denver	Product 2	1.0
4	Washington	Product 2	0.5

What i want to archive in Power Bi is something like this:

City	Product 1	Product 2
New York	21.2
Chicago	21.3
Denver		31.00
Washington		30.50

I want to sum (price + distribution fee) according to the city  is sold. But I'm not sure how to link the tables, or which measure should I use. Can someone help me?

[Ashish_Mathur]

Hi,

Assuming there will never be a repetition in the Product column 1 of Table 1 AND there is a relationship (Many to One and Single) from Table 2 to Table1, write these calculated column formulas in Table2

DF = related('Table1'[Price])

Total = [Distribution fee]={df]

Create a matrix visual and write this measure

Measure = sum([Total])

Hope this helps.

[NewUser3300]

I actually used your anwser to solve the problem, thank you

[Ashish_Mathur]

You are welcome.

[Irwan · Accepted solution]

hello @NewUser3300 

please check if this accomodate your need.

1. create a relationship many-to-many

2. create a new measure with following DAX

Total Sum = 
SUMX(
    FILTER(
        'Distribution',
        'Distribution'[Product]=SELECTEDVALUE('Price'[Product])
    ),
    SELECTEDVALUE('Price'[Price])+'Distribution'[Distribution Fee]
)

3. plot into matrix visual

Hope this will help.

Thank you.