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.
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.