Hey guys,
I am currently facing an issue that I use measures in a table, which should only consider certain filters but not all filters applied. I tried many combinations of all, allexcept, allselected, and removefilters, but I am not getting there.
The target table should look as follows:
| Customer | Product | Sales | # Storages |
| A | P1 | 200 | 3 |
| A | P2 | 400 | 3 |
| B | P1 | 300 | 2 |
| ... | ... | ... | ... |
My target:
- I would like the measure "Sales" to respect the filtering within the table, so "Customer" and "Product" dimension. I would also like the measure to respect the filters "Year" and "Product" from outside, but no others filters (e.g. "Sales Region")
- I would like the measure "Storages" to respect only filtering by "Customer" within the table (but not "Product") and only "Year" and "Products" from outside the table (but not "Sales Region" etc.).
Current measures:
- Sales = SUM ( 'Orders'[Net] )
- Storages = Countdistinct ( 'Orders'[StorageID] )
Filter pane:
- Year = 2022
- Products: P1 and P2
- Sales region: EMEA
- ... (more filters)
My input data look as follows:
Orders:
| CustomerID | ProductID | Date | Net | StorageID |
| C987 | 147852 | 01.01.2022 | 10 | S123 |
| C654 | 369258 | 02.01.2022 | 15 | S456 |
Customer data:
| CustomerID | CustomerName | Sales Region | ... |
| C987 | A | EMEA | ... |
| C654 | B | LATAM | ... |
Product data:
| ProductID | ProductName | ... |
| 147852 | P1 | ... |
| 369258 | P2 | ... |
How do I need to edit the measures to have the aimed effect?
Thanks and best
T
