cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sau001
Helper IV
Helper IV

Ignore filter that has been set by slicer

Hi All,

Please consider the following:

Model

I have the tables

  • Sales
  • SalesPersons
  • Categories

sau001_0-1610843573496.png

 

Objective

sau001_1-1610844026073.png

 

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

 

1 ACCEPTED SOLUTION
parry2k
Super User III
Super User III

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






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

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

4 REPLIES 4
parry2k
Super User III
Super User III

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

 

 






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

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





parry2k
Super User III
Super User III

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






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

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





parry2k
Super User III
Super User III

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






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

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

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

 

 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors