Share Calculation

[dokat]

Hi,

I'd like to calculate share based on Region Sales divided by total sales. I created below table by removing interactions between slicer and the table. Sum of my sales values are $2,535,250,045 and breakdown by region is like below. I have additional measures in the same table that needs to interact with the slicer selections therefor i can't calculate share by adjusting interactions,

Currently Region Test Measure 2 values change based on slicer selection. Is there anyway to adjust the formula that exclude slicer selection. Basically gives me a static region total so i can calculate share of region regardless of the slicer selection. Appreciate any help!

Region Test Measure2 = CALCULATE(Calculate(SUM('Region'[Values]),'POS CY'[Slicer] IN {"L52W"}))

[vojtechsima]

Hi, @dokat ,
If you just add:
ALL([Your Slicer Table Column]
as one of the filters in your CALCULATE expression. It will ignore any filtering applied by this column.

So try playing with something like this:

Region Test Measure2 = CALCULATE(Calculate(SUM('Region'[Values]),ALL([Your SlicerTable Column],'POS CY'[Slicer] IN {"L52W"}))

[dokat]

@vojtechsima @Thank you for your response. I have 4 slicers, is there a way to expand this formula to include all slicers?

[vojtechsima · Accepted solution]

@dokat 
Sure, you either use ALL([Column1], [Column2], [Column3], [Column4]) or you can mention columns that you want to keep filtered ALLEXPECT([Column6]) for example.

[dokat]

@vojtechsima Thank you it worked!

[vojtechsima]

@dokat Glad to hear that.