Sum based on filtered values

[badger123]

Hello,

I'm hoping someone can help with this. I have tried to illustrate my problem with simplified tables and data below. See here for the pbix file: https://drive.google.com/open?id=1emMTSkXIrs0D8ZIjVURqC0yLf_70Ma3k

I have items and terms in Table 1 - the same term can be against multiple items. I am using Item from Table 3 as a slicer which works in an exclude manner, so if an item is selected (e.g. item two) this excludes all terms that are tagged against item two (see measure 1). Then for remaining terms (those not excluded) the values are showing (see measure 2).  

Measure1 = CALCULATE(COUNT('Table3'[Term ID]), CROSSFILTER(Table3[Term ID], Table1[Term ID], both))

Measure 2 = IF(isblank([Measure1]),SUM(Table2[Values]), BLANK())

What I'm trying to achieve is to apply the same logic without having to put term as a field in a table visual. Specifically, I want to show the items based on the terms that haven't been excluded from selecting an item, and sum the values of the non-excluded terms against the items. Using the example above, I would like the table visual to show: 

Item	Values
item one	3930
item three	2550

Is this possible? Any advice would be much appreciated.

[TeigeGao]

Hi @badger123 ,

The pbix file you shared requires a permission, please grant the permission to me or share it via other storage.

Best Regards,

Teige

[badger123]

Hey @TeigeGao ! Sorry, I shared the wrong link: https://drive.google.com/open?id=1emMTSkXIrs0D8ZIjVURqC0yLf_70Ma3k

Hopefully that should let you download the pbix file 🙂