Lookupvalue with Sum

[kivanct]

Hi,

I would be appriciated if you could help me. I have two tables and both have duplicate values. So i am unable to use LOOKUPVALUE. 

MY FORMULA : LOOKUPVALUE = LOOKUPVALUE(ZS55[Miktar],ZS55[MAMUL NO],ZD83[Malzeme])

I have two tables == ZD83 & ZS55

ZS55 where I would like to get values of "Miktar" column. 

My result table; 

How can i get the sum of values located in "ZS55" table , "Miktar" Column ?

[Anonymous · Accepted solution]

Hi  @kivanct ,

I created some data:

ZD83：

ZS55：

Here are the steps you can follow：

1. Create calculated column.

Way1 =
SUMX(
    FILTER(ALL(ZS55),
    'ZS55'[MAMUL NO]=EARLIER('ZD83'[Malzeme])),[Miktar])

Way2 =
var _select=SELECTCOLUMNS('ZS55',"1",'ZD83'[Malzeme])
return
SUMX(FILTER(ALL(ZS55),
'ZS55'[MAMUL NO] in _select),[Miktar])

2. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[amitchandak]

@kivanct , Try like

LOOKUPVALUE = maxx(filter(ZS55,  ZS55[MAMUL NO] =ZD83[Malzeme]) , ZS55[Miktar])

or

LOOKUPVALUE = concatenatex(filter(ZS55,  ZS55[MAMUL NO] =ZD83[Malzeme]) , ZS55[Miktar], ", ")