cancel
Showing results for
Search instead for
Did you mean:
Member

## Filter that ignores some slicers, but respects the other

Hi,

Can anybody help me to figure out how to build a measure that ignores one slicer while respecting the other? I have a Sales table and three slicers - Year, Month and Sales Manager. Year, Month fields are from the related Date table, Sales Manager field is from the Sales table. I need to sum up sales amount for some ProductID respecting date slicers and ignoring Sales Manager slicer.  Something like this, but I can't get it work :

```Total_of_table2_visual =
SUMMARIZE (
FILTER (
ALLEXCEPT ( Date, Date[Year], Date[Month] ),
Sales[ProductID] IN { "123" }
),
"zz", SUM ( Sales[Amount] )
)```

It does not allow me to use Sales table if it is not mentioned in ALLEXCEPT even though Date is related to Sales.

I know this standalone example doesn't make a lot of practical sense, but it is part of a more complex calculation where I want to figure out cost distribution that is not affected by selection of a manager.

1 ACCEPTED SOLUTION

Accepted Solutions
Member

## Re: Filter that ignores some slicers, but respects the other

OK, this one does the trick. It also respects Dates table rather just working on the native Date column.

```Total sales =
VAR xMytable =
ADDCOLUMNS (
SUMMARIZE (
FILTER ( ALL ( Sales ), Sales[ProductID] IN { "111" } ),
Sales[Date],
Sales[Manager],
Sales[ProductID]
),
"zz", SUM ( Sales[Amount] )
)
RETURN
CALCULATE ( SUM ( Sales[Amount] ), xMytable )```

6 REPLIES 6
Super User

## Re: Filter that ignores some slicers, but respects the other

Hi @gvg,

You need to do a measure similar to this

Sales all managers =CALCULATE ( SUM( SALES[Amount]) ; ALL(SALES[Manager] ))

Regards
MFelix

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Member

## Re: Filter that ignores some slicers, but respects the other

@MFelix , yes, this works. But my measure I am trying to tweak should place the total amount in each row in the visual.

Super User

## Re: Filter that ignores some slicers, but respects the other

HI @gvg,

Without any data or expected result is difficult to give you a good answer.

Can you share some sample data and expected resutl?

Regards,

MFelix

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Highlighted
Member

## Re: Filter that ignores some slicers, but respects the other

OK, this is my original Sales table:

And this is what I expect as a result:

I need to get Total Sales for a specific product on each line in the visual (to be able to use that number in further calculation), that respects Date slicer and ignores Manager slicer. My following measure does the trick except that I can not get it work when some Manager is selected in slicer. When I select John, it should show only lines for John with all the numbers for John but with Total sales unchanged (i.e. =140).

```Total sales =
SUMMARIZE(
FILTER (
ALLEXCEPT(Sales,Sales[Date]),
Sales[ProductID]  IN { "111" }
),
"zz", SUM ( Sales[Amount])
)```

Ideally I'd like to use Dates table as a slicer for dates.

Here's my test file.

Member

## Re: Filter that ignores some slicers, but respects the other

OK, this one does the trick. It also respects Dates table rather just working on the native Date column.

```Total sales =
VAR xMytable =
ADDCOLUMNS (
SUMMARIZE (
FILTER ( ALL ( Sales ), Sales[ProductID] IN { "111" } ),
Sales[Date],
Sales[Manager],
Sales[ProductID]
),
"zz", SUM ( Sales[Amount] )
)
RETURN
CALCULATE ( SUM ( Sales[Amount] ), xMytable )```

Super User

## Re: Filter that ignores some slicers, but respects the other

Glad you could solve it.

Don't forget to mark the response as correct.

Regards,

MFelix

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!