cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
gvg Member
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
gvg Member
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
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!




Highlighted
gvg Member
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
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!




gvg Member
Member

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

OK, this is my original Sales table:

 

pic1.JPG

 

And this is what I expect as a result:

 

pic2.JPG

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.

gvg Member
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
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!