Forum Discussion

Grizman's avatar
Grizman
Helper I
8 years ago
Solved

Fields

I have two table:

 

 

which reveal GrossTurnever=Q-ty*ProductPrice for every year and Margin=GrossTurnever2-GrossTurnever1

 

Need Help
  • Thyago_Rezende's avatar
    Thyago_Rezende
    8 years ago

    First picture below shows bi-directional relationship between tables. 

     

    Second picture shows measure for year 2016. 

     

    Hope help you. 

    Best regards,

20 Replies

  • Thyago_Rezende's avatar
    Thyago_Rezende
    Resolver I
    8 years ago

    Hi,

     

    First, you need to transform both columns year and its values in 2 columns only. 

     

    After that you can use:
    GrossTurneverYear2016 = 
    SUMX  ( 
                 FILTER (

                              Table#2;
                              PriceYear = 2016
                 );
                 PriceValue2016 * RELATED ( Table#1[Q-ty])
    )

    Regards,
                                    
                         

  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    Community Support
    8 years ago

    Hi Grizman,

     

    In your scenario, please open Query Editor, rename columns in second table to 2015, 2016, 2017, then select all these three columns, click Unpivot Columns button, apply the changes.

     

    Then create a measure:

     

    Diff =
    var varMAX=MAXX(ALLSELECTED(Table2[Year]),[Year])
    var varMin=MINX(ALLSELECTED(Table2[Year]),[Year])
    return
    (LOOKUPVALUE(Table2[Value],'Table2'[Year],varMAX,'Table2'[Id_product],MAX('Table1'[Id_product])) - LOOKUPVALUE(Table2[Value],'Table2'[Year],varMin,'Table2'[Id_product],MAX('Table1'[Id_product])))*MAX('Table1'[Q-ty])

     

     

    For details, you can download attached pbix file to have a look.

     

    Best Regards,
    QiuyunYu