Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi All,
Please consider the following:
Model
I have the tables
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
Solved! Go to 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.⚡
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
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.
@Anonymous Having said that REMOVEFILTERS from the Category table is a better approach as you can use any column from the Salesperson table.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
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.
@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.⚡
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
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.
@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.⚡
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
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.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
85 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |