Forum Discussion
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:
| Item | Values |
| item one | 3930 |
| item three | 2550 |
Is this possible? Any advice would be much appreciated.
2 Replies
- badger123Resolver I
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 :)