Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

create a table for price difference

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

 

 

1. table.

ReferenceRetailerPrice
A modelH&m100
B modelH&m200
C modelZara150
D modelZara250

 

2 Table.

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

ReferencePrefixATA mapping model
A modelAC
B modelBD

 

 

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)

PrefixATA 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's avatar
    bhanu_gautam
    1 year ago

    Anonymous , 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".

1 Reply

  • bhanu_gautam's avatar
    bhanu_gautam
    Super User
    1 year ago

    Anonymous , 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".