Forum Discussion
Share Calculation
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"}))
Anonymous
Sure, you either use ALL([Column1], [Column2], [Column3], [Column4]) or you can mention columns that you want to keep filtered ALLEXPECT([Column6]) for example.
5 Replies
- vojtechsima
Super User
Hi, Anonymous ,
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"}))
vojtechsima @Thank you for your response. I have 4 slicers, is there a way to expand this formula to include all slicers?
- vojtechsima
Anonymous
Sure, you either use ALL([Column1], [Column2], [Column3], [Column4]) or you can mention columns that you want to keep filtered ALLEXPECT([Column6]) for example.