Ignore 2 of 3 slicers on a page

[PaalA]

As I try to make a summary page I have 3 slicers on the page(Slicers for column "Material=Food A", "Material duplicate 1=Food B" and "Material duplicat 2= Food C"). I need a formula to calculate the cost for the material filtred for in "Material", but when i try the formula below I get no value (only get "Blank"). It seems like it takes alle the three filters into account, because if i turn of interactions with 2 of the slicers it calculates the cost. 

Total cost product 1 = CALCULATE(SUMX(Table1,-Table1[kost1]-Table1[kost2]),ALLEXCEPT(Table1,Table1[Material]))

Material	Material duplicate 2	Material duplicate 2	kost1	kost2
Food A	Food A	Food A	10	12
Food b	Food b	Food b	15	14
Food c	Food c	Food c	13	12
Food d	Food d	Food d	11	17

If anyone can help me with a formula that works for the scenario given above, it's hihgly appreceated!

[v-juanli-msft]

Hi @PaalA 

As parry2k suggested, create two sperate tables,

compare1 = VALUES('Table'[Material])

compare2 = VALUES('Table'[Material])

Add [Material1] and [Material2] into the slicers.

Create measures

compare_value1 = CALCULATE(SUM('Table'[kost1])+SUM('Table'[kost2]),FILTER('Table','Table'[Material]=SELECTEDVALUE(compare1[Material1])))

compare_value2 = CALCULATE(SUM('Table'[kost1])+SUM('Table'[kost2]),FILTER('Table','Table'[Material]=SELECTEDVALUE(compare2[Material2])))

per material = CALCULATE(SUM('Table'[kost1])+SUM('Table'[kost2]),ALLEXCEPT('Table','Table'[Material]))

Edit interactions as below

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PaalA]

Hi,

Thank you for a good and fast answer.

I have one follow up question though: I'm going to use all the values in one diagram (e.g. pie chart). It don't seems to be working when doing this because I can't turn of the interaction between slicer 1 and the diagram without getting the whole sum of the table.

Is there a way to solve this so i can use the values together in diagrams to make a summary? 

Thanks a lot for your help!

[v-juanli-msft · Accepted solution]

Hi @PaalA 

Create measures

per material = SUM('Table'[kost1])+SUM('Table'[kost2])

Measure 1 = CALCULATE([per material],FILTER('Table','Table'[Material]=SELECTEDVALUE(compare1[Material1])))

Measure 2 = CALCULATE([per material],FILTER('Table','Table'[Material]=SELECTEDVALUE(compare2[Material2])))

Measure 3 = CALCULATE([per material],FILTER('Table','Table'[Material]=SELECTEDVALUE(compare3[Material3])))

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[parry2k]

@PaalA You have to create a separate table for each slicer and make no relationship with your data table and then use values from these slicers to filter your data table.