Ignore filter that has been set by slicer

[Anonymous]

Hi All,

Please consider the following:

Model

I have the tables

• Sales
• SalesPersons
• Categories

Objective

Create a measure which will show the Total Revenue per Sales person disregarding the category in the Slicer. We want to compare how a specific sales person performed in the categories selected vs total sales logged by that sales person.

What worked?

TotalRevenueAcrossAllCategories = CALCULATE( SUM(Sales[Revenue]) , REMOVEFILTERS(Categories))

What did not work?

TotalRevenueAcrossAllCategories2 = CALCULATE( SUM(Sales[Revenue]) , ALLEXCEPT(Sales, Sales[SalesPersonID]))

Question

I was able to achieve my objective using the REMOVEFILTERS function.  I think ALLEXCEPT is the one that should be here and I couldn't get it work? Any suggestions?

Sample PBI

https://1drv.ms/u/s!AlNqzWdWxOHSnFcZQEWlDlc1Uuyo?e=Q8DIsa

Thank you,

Sau

[parry2k]

@Anonymous Having said that REMOVEFILTERS from the Category table is a better approach as you can use any column from the Salesperson table.

[parry2k]

@Anonymous First of all the measure expression I sent, I had a parenthesis at the wrong place., it supposes to be

TotalSalesAcrossAllCategories  = 
CALCULATE( SUM(Sales[Revenue]) , ALLEXCEPT(Sales, SalesPerson[Name],SalesPerson[ID]))

Now the measure you gave if you post only SalePerson(Id) in AllExcept and when you visualizing the data, you don't have the ID and just the name of the sales person this measure will not work. To test, keep your measure, remove Id from the table visual and I expect the result to be wrong.

Check my latest blog post Year-2020, Pandemic, Power BI and Beyond to get a summary of my favourite Power BI feature releases in 2020

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[parry2k · Accepted solution]

@Anonymous this would work because you are having id and name from salesperson table, not from sales table.

TotalSalesAcrossAllCategories  = 
CALCULATE( SUM(Sales[Revenue]) , ALLEXCEPT(Sales, SalesPerson[Name]),SalesPerson[ID])

Check my latest blog post Year-2020, Pandemic, Power BI and Beyond to get a summary of my favourite Power BI feature releases in 2020

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Anonymous]

Hello @parry2k ,

Yes - your DAX expression worked.    Just one final question.

I removed the column from your DAX expression and it worked.

After removing SalesPerson[Name]

TotalSalesAcrossAllCategories4 = 
CALCULATE( SUM(Sales[Revenue]) , ALLEXCEPT(Sales, SalesPerson[ID]))

Your DAX

TotalSalesAcrossAllCategories  = 
CALCULATE( SUM(Sales[Revenue]) , ALLEXCEPT(Sales, SalesPerson[Name]),SalesPerson[ID])

And it worked!   Why?

Thanks,

Sau