ALLEXCEPT stops working as expected when i remove a field from my visual. What am i missing?

[03clacey]

I'm trying to create a "Market Size" formula that calculates the relevant market size per brand. 

"Market Size" is considered as the sum of sales for all countries that brand is sold in.

Example: If a brand sells in the UK & France, i want to return the SUM of Sales for all brands within UK & France. 

I’ve been using ALLEXCEPT, and this works fine when I have the Country field in my visual, but if take it out of my table it no longer works as expected, and instead returns the SUM of Sales for the entire dataset, for every row in my visual. 

Every search I do comes back to ALLEXCEPT, but I’m clearly missing something, or using the wrong formula. 

My calculation is:

CALCULATE(    SUM('Sales'[Sales]),    ALLEXCEPT('Sales', 'Sales'[Country])    )

Any help would be greatly appreciated.

[lbendlin · Accepted solution]

ALLEXCEPT is a rather blunt tool. Try something more surgical like REMOVEFILTERS.

[v-nmadadi-msft]

Hi @03clacey,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the community members for the issue worked. If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Thanks and regards

[v-nmadadi-msft]

Hi @03clacey ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If our responses has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[v-nmadadi-msft]

Hi @03clacey,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[Deku · Accepted solution]

Var countries = values( 'Sales'[Country] )

Return

CALCULATE(

SUM('Sales'[Sales]),

ALLSELECTED(),

countries

)

[lbendlin · Accepted solution]

ALLEXCEPT is a rather blunt tool. Try something more surgical like REMOVEFILTERS.