create a table for price difference

[yjsim]

Hi all  i want to create an table in power bi but I don't know how to do.

1. table.

Reference	Retailer	Price
A model	H&m	100
B model	H&m	200
C model	Zara	150
D model	Zara	250

2 Table.

*ATA : apple to apple so i want to compare the price of each ATA models.

Reference	Prefix	ATA mapping model
A model	A	C
B model	B	D

I want to make this table in power bi but I don't know how to pull up the data like below.

AT H&M (should be filtered)

Prefix	ATA price diffrence
C	=150/100

I have more than 100 lines for each model and the below are just example, so when i make a filter dax code, i can't specify only one model. Could anyone help this?

[bhanu_gautam · Accepted solution]

@yjsim , Try using 

Step 1: Load Data
Load your data into Power BI. You should have two tables: Table1 and Table2.

Step 2: Create Relationships
Ensure there is a relationship between Table1[Reference] and Table2[Reference].

Step 3: Create Calculated Columns
In Table1, create the following calculated columns:

ATA Mapping Model Column:

ATA Mapping Model =
LOOKUPVALUE(
Table2[ATA mapping model],
Table2[Reference], Table1[Reference]
)

ATA Price Column:

ATA Price =
LOOKUPVALUE(
Table1[Price],
Table1[Reference], Table1[ATA Mapping Model]
)

Step 4: Create the Measure for ATA Price Difference
Create a measure to calculate the ATA price difference:

ATA Price Difference Measure:

ATA Price Difference =
DIVIDE(
MAX(Table1[ATA Price]),
MAX(Table1[Price])
)
Step 5: Create the Visual
Add a table visual to your report.
Add the Prefix column from Table2 and the ATA Price Difference measure to the table visual.
Apply a filter to the visual to show only rows where Retailer is "H&M".