Forum Discussion
Sum with all and allselected
efanta The answer depends on what will be in the report level filter and what will be in the slicers. If you are using a column in both a slicer and a report level filter, what you're asking for is impossible. If the report level filter will always be a column that isn't used anywhere else and will never need to affect this sum, it's possible.
Let's say you have a report level filter that says TableName[FilterColumn] = "X" and you have slicers on your report for TableName[SlicerColumn1], TableName[SlicerColumn2] and TableName[SlicerColumn3]. You want your sum of ColumnName to ignore FilterColumn, but respect selections for SlicerColumn1, 2 and 3. You can do that with this measure:
Selected Sum = CALCULATE( SUM(TableName[ColumnName]),
ALLEXCEPT( TableName,
TableName[SlicerColumn1], TableName[SlicerColumn2], TableName[SlicerColumn3])
)
Note that if you set a report level filter for TableName[SlicerColumn1] = "Y" it will not be ignored. The measure doesn't care whether it's getting the filter context from a slicer or a report level filter or a selection on some other visual; it only cares that the measure says it should not ignore filters coming from SlicerColumn1.
Community Championefanta If I understood you correctly what you want is "sum of sales without effect of slicers but apply effect of fitlers" then you don't even need to create a measure. Simple drop Sales field onto your visual then select your slicers under Visual Tools click Edit Interactions and choose None. This means slicers won't have any effect but report level filter will. As said by Anonymous it depends on what I understood.
Thanks guys for your anwsers! But the idea is "sum of sales without effect of report level filter but apply effect of slicers" (Keeping the report level filter in the whole page).
efanta The answer depends on what will be in the report level filter and what will be in the slicers. If you are using a column in both a slicer and a report level filter, what you're asking for is impossible. If the report level filter will always be a column that isn't used anywhere else and will never need to affect this sum, it's possible.
Let's say you have a report level filter that says TableName[FilterColumn] = "X" and you have slicers on your report for TableName[SlicerColumn1], TableName[SlicerColumn2] and TableName[SlicerColumn3]. You want your sum of ColumnName to ignore FilterColumn, but respect selections for SlicerColumn1, 2 and 3. You can do that with this measure:
Selected Sum = CALCULATE( SUM(TableName[ColumnName]),
ALLEXCEPT( TableName,
TableName[SlicerColumn1], TableName[SlicerColumn2], TableName[SlicerColumn3])
)
Note that if you set a report level filter for TableName[SlicerColumn1] = "Y" it will not be ignored. The measure doesn't care whether it's getting the filter context from a slicer or a report level filter or a selection on some other visual; it only cares that the measure says it should not ignore filters coming from SlicerColumn1.
