Ignore a single slicer on a measure

[SuchCT]

Hello,
I currently have a page with several slicers from different tables(they all have relationships).
But I'm trying to find the percentage of Weight this Product represents of the total Weight given all the selected slicers. So I´d need both a Weight affected by every filter. And a Weight that´s affected by every filter besides Product.

For Example this my full table. If I needed what percentage of Green Product C was sold in USA out of the total Weight. I´d need:
-Weight of C (Affected by Product, Color, Country) = 1
-Total Weight (Affected by Color, Country but NOT Product) = 33

Measure Result=1/33

Product         Model       Color          Weight     Country

A	2	Green	9	USA
C	3	Green	1	USA
A	2	Red	6	MEX
C	2	Green	1	MEX
B	2	Green	8	USA
B	1	Red	9	CAN
A	1	Green	4	MEX
A	1	Red	9	CAN
B	1	Blue	7	MEX
C	1	Red	9	CAN
A	2	Green	10	USA
B	2	Blue	5	MEX
C	3	Blue	4	MEX
C	1	Blue	1	MEX
A	3	Red	1	USA
B	2	Green	5	USA
B	3	Green	10	MEX
C	3	Blue	9	CAN
C	2	Green	2	MEX
B	3	Green	7	MEX

Thanks in advance

[MFelix · Accepted solution]

Hi @SuchCT ,

For this you need to use the ALLEXCEPT using the two columns you need:

Weight for product = 
DIVIDE (
    SUM ( 'Table'[Weight] ),
    CALCULATE (
        SUM ( 'Table'[Weight] ),
        ALLEXCEPT ( 'Table', 'Table'[Country], 'Table'[Color] )
    )
)

Check result below:

[SuchCT]

This one sort of works. I can´t really add everything I need because the filters interact with each other, so it's not as easy as too just add them on ALLEXCEPT.

I'll accept it as solution as to help anyone else. Thanks for the help.

[MFelix]

Hi @SuchCT ,

What do you mean "sort of works" were is this lacking the flexebility you need?

[SuchCT]

I think the problem comes from the iflters interacting with each other. I have a lot of slicers on this page. Continuing with the example if I were to filter by "Color" and "Model" on the slicers that would automatically modify the other slicers. 
So now, eventhough I added "Color" and "Model" to ALLEXCEPT, the other parameters ("Country" and "Products") are being modified as well.
The workaround I found is to just add to the ALLEXCEPT the Fields that remain constant, the more "general" ones.

Excuse me if I'm not expressing myself clearly, english is not my first language.

[MFelix]

Hi @SuchCT ,

This can happen when you use a single table for your data, because the filtering impacts other slicer the option would be to have disconnected tables for the slicer and then make the calculation using the values selected on those slicers but that would also give you some other issues.

(no problem about the english is not mine first language also).

If you need anything else please tell me.

[SuchCT]

Thanks for your reply, I found that method and am using it right now. But I was looking for a more "hands off" solution. In case in the future someone needs to add a new filter, they don't have to modify the measure.
If that's not possible I'll accept your post as solution. Thanks again.

[MFelix]

Hi @SuchCT ,

In this case you need to use the following syntax:

Weight for product = 
DIVIDE (
    SUM ( 'Table'[Weight] ),
    CALCULATE (
        SUM ( 'Table'[Weight] ),
        ALL ( 'Table'[Product] )
    )
)

[PaulDBrown]

Try:

Weight for product = 
DIVIDE (
    SUM ( 'Table'[Weight] ),
    CALCULATE (
        SUM ( 'Table'[Weight] ),
        ALL ( 'Table', 'Table'[Product] )
    )
)