Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
gvg
Post Prodigy
Post Prodigy

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
gvg
Post Prodigy
Post Prodigy

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 )

  

View solution in original post

6 REPLIES 6
MFelix
Super User
Super User

Hi @gvg,

You need to do a measure similar to this

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

Regards
MFelix

Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



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

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


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



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
Post Prodigy
Post Prodigy

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 )

  

Glad you could solve it.

 

Don't forget to mark the response as correct.

 

Regards,

MFelix


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.