Calculate without select all filter

[Mishelski]

I have 2 filters on my report, one for the entity and one for the market that the entity interacts with.

In the matrix I have created the 'All Lots' value, in this example the all lots value should be filtered by the entity and the markets - the issue Im having is that the filter isnt working as intended. When I select a entity and then select the 'select all' option for the market for some reason this seems to factor in additional markets that this entity is not trading in. However if I uncheck select all and individually select all the available markets in the filter, the value displays correctly .

Total lots w/o filters = CALCULATE(SUM(BROKER_TRADE_ANALYSIS[LOTS]), REMOVEFILTERS(BROKER_TRADE_ANALYSIS[LEGAL_ENTITY_ID]))

Example 1 (with select all on the Market ID filter - incorrect value)

 

Example 2 (with manually selecting all the options - correct value)

Any help would be appreciated thanks

[Mishelski]

Hi, thats gives me the total lots without factoring in the entire market 

What im trying to achieve is where the value will show the total amount of lots for the market id

Broker lots = the number of lots the broker has

all lots = the number of lots that market id has, but only if the broker has traded in that market 

This is what im trying to achieve but whats happening is the 'select all' part of the market ID filter is including markets that the broker does not trade in, therefore inflating that value to include every market.

[DimaMD]

Hi, @Mishelski try it measure

Value_total = CALCULATE( SUM('table'[value]), ALLEXCEPT('table','table'[market]) )

[NikhilChenna]

Hi @Mishelski ,

Can you check and let me know what value are you getting, comment the REMOVEFILTERS part from the dax code.

Regards, 
Nikhil Chenna 

Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!