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,

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