Forum Discussion

badger123's avatar
badger123
Resolver I
7 years ago

Sum based on filtered values

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: 

 

ItemValues
item one3930
item three2550

 

Is this possible? Any advice would be much appreciated.