Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Get Values from table1 for corresponding values from table2 with duplicates

I have 2 tables, table1 with ID and table2 with corresponding old ID. I want a calculation where  ID from table1 and correspoding OLD ID in table2 need to consider and then need values(quantity, pric...
Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    5 years ago

    Hi  Anonymous ,

     

    Create 2 columns as below:

    _count = CALCULATE(COUNTROWS('Table1'),FILTER('Table1','Table1'[Year]=2019&&'Table1'[ID]=EARLIER(Table1[ID])))
    _sum = 
var _qty=CALCULATE(MAX('Table1'[ Quantity]),FILTER('Table1','Table1'[Year]=2020&&'Table1'[ID]=EARLIER('Table1'[ID])))
var _price=CALCULATE(MAX('Table1'[Price]),FILTER('Table1','Table1'[Year]=2019&&'Table1'[ID]=EARLIER(Table1[ID])))
Return
IF('Table1'[_count]=1,_qty*_price)

    And modify measure 2019 as below:

    Value(2019) = 
var _sum=SUMX(FILTER(ALL(Table1),'Table1'[Year]=2020&&'Table1'[new ID]=MAX('Table1'[new ID])),'Table1'[ Quantity])
var _price=SUMX(FILTER(ALL(Table1),'Table1'[Year]=2019&&'Table1'[new ID]=MAX('Table1'[new ID])),'Table1'[Price])
var _aver=DIVIDE(_price,MAX('Table1'[_count]))
var _newID=CALCULATETABLE(VALUES('Table1'[new ID]),'Table1'[_count]>1)
Return
IF(MAX('Table1'[new ID]) in _newID,_sum*_aver,SUMX(SUMMARIZE('Table1','Table1'[ID],'Table1'[new ID],"price",MAX('Table1'[_sum])),[price]))

    And you will see:

    For the related .pbix file,pls see attached.

     

    Best Regards,
    Kelly

    Did I answer your question? Mark my post as a solution!

Anonymous's avatar
Anonymous
Not applicable
5 years ago

v-kelly-msft Consider below data set and calculations. Consider the changes highlighted in yellow. Also in the calculation for 2019, i need to multiply price of 2019 with qty of 2020. pls see calculation highlighted in red for better uderstanding.

 

 

v-kelly-msft's avatar
v-kelly-msft
Community Support
5 years ago

Hi  Anonymous ,

 

Create 2 columns as below:

_count = CALCULATE(COUNTROWS('Table1'),FILTER('Table1','Table1'[Year]=2019&&'Table1'[ID]=EARLIER(Table1[ID])))
_sum = 
var _qty=CALCULATE(MAX('Table1'[ Quantity]),FILTER('Table1','Table1'[Year]=2020&&'Table1'[ID]=EARLIER('Table1'[ID])))
var _price=CALCULATE(MAX('Table1'[Price]),FILTER('Table1','Table1'[Year]=2019&&'Table1'[ID]=EARLIER(Table1[ID])))
Return
IF('Table1'[_count]=1,_qty*_price)

And modify measure 2019 as below:

Value(2019) = 
var _sum=SUMX(FILTER(ALL(Table1),'Table1'[Year]=2020&&'Table1'[new ID]=MAX('Table1'[new ID])),'Table1'[ Quantity])
var _price=SUMX(FILTER(ALL(Table1),'Table1'[Year]=2019&&'Table1'[new ID]=MAX('Table1'[new ID])),'Table1'[Price])
var _aver=DIVIDE(_price,MAX('Table1'[_count]))
var _newID=CALCULATETABLE(VALUES('Table1'[new ID]),'Table1'[_count]>1)
Return
IF(MAX('Table1'[new ID]) in _newID,_sum*_aver,SUMX(SUMMARIZE('Table1','Table1'[ID],'Table1'[new ID],"price",MAX('Table1'[_sum])),[price]))

And you will see:

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!