How to check if an item is present in 2 categories selected via slicer

[Meru]

 I have a table containing Item, Year, Price & Quantity. Now for one calculation I want to check if an item is present in 2 years which usr has selected via slicers. If item is present in both years then those only need to consider for calculation. Consider below image, here if user select year1 as 2018 and year2 as 2019 then we need to consider all the items(1,2,4) which are present in both years. need measure to calculate (Year2 quantity * Year1 Price, itemwise)                                                 

[Vera_33]

Hi @Meru 

Based on your sample data, you have two disconnected Year slicers, then

result = 
VAR vYear1= SELECTEDVALUE(Year1[Year])
VAR vYear2= SELECTEDVALUE(Year2[Year])
RETURN
MAXX(FILTER(factTable,factTable[Year]=vYear2),factTable[Quantity])
*
MAXX(FILTER(factTable,factTable[Year]=vYear1),factTable[Price])

[Meru]

Hi @Vera_33 

Thanks for your help. This way I can find the itemwise total as per required calcultion but when I need total of all the items for selected year its giving wrong result. Total = Sum of (Year1 price itemwise* Year2 quantity itemwise) and exclude all the items from calculation which are not present in both of the selected years. Also pls suggest...I also need a calculation where quantity(2019) * price (2019) also required and then need to compare with Quantity(2019) * Price(2018) column. If item is not present in 2018 then remove from both calculations.

[Vera_33]

Hi @Meru 

try it

cal1 = SUMX(VALUES(factTable[Item]),[result])

cal2 = 
VAR vYear2= SELECTEDVALUE(Year2[Year])
VAR T1=FILTER(factTable,factTable[Year]=vYear2)
RETURN
SUMX(T1,factTable[Quantity]*factTable[Price])

[Meru]

@Vera_33 

Thanks for your effort, its working fine but in cal2 its also giving items which were not sold in year1(2018) Consider below image, i don't want row 3 here and need to filter it by calculations only not by filter pane.

. 

[Vera_33 · Accepted solution]

try it

cal2 = 
VAR vYear2= SELECTEDVALUE(Year2[Year])
VAR T1=FILTER(factTable,factTable[Year]=vYear2)
RETURN
IF([result]=BLANK(),BLANK(),
SUMX(T1,factTable[Quantity]*factTable[Price]))

[Meru]

Thanks a ton. It worked 😀