Sum with all and allselected

[efanta]

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,

[ankitpatira]

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

[efanta]

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

[KHorseman · Accepted solution]

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

[Anonymous]

If I understand what you are looking for, it is basically "gimme a sum, ignoring any filtering on the ID column".  I think you sorta need to think of it this way... "what columns do I want impacted by filters/slicers?".

Total Sales = CALCULATE (SUM ( 'DB' [Sales]), ALL ( 'DB'[ID]) )

Which is "remove the filter on the ID column".  '

But it all started with that "if I understand..." 🙂

[efanta]

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,