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.
Hi Folks,
I made a report where I placed in the Report Level Filter "is not blank" for a column. In a new worksheet, I´m trying to sum all the items if a the table (ALL expression) but at the same time consider the filters of the page (slicers).
I created a table for the purpose of explanation:
Date Sales ID
jan-2016 1000 1
jan-2016 2000 2
jan-2016 3000
Feb 16 1500 2
Feb 16 1500 1
In the report, I placed in Report level filter "Is not blank" for "ID" column. The report has a slicer´s date (options jan-2016 and feb-2016 ) and ID (only 1 and 2, blank data is filtered). I created a measure Total Sales, and if I select jan-2016, only sums 3.000 (1.000 + 2.000), due to it is not considering the blank row. The goal is to sum all sales considering all the slicers without removing the report level filter. For example, to jan-2016, the idea is to sum 6.000, but when I sum Total Sales considers jan-2016 and Feb-2016, all together without differentiating by month, although I have the slicer month selected in jan-2016.
I tried to use:
Total Sales = CALCULATE (SUM ( 'DB' [Sales]), ALL ( 'DB'), ALLSELECTED ( 'DB' [Date]))
But I do not have the expected results.
Any suggestions Which Function Should I use?
BR,