Removing filters for certain measures

[Imrans123]

Hello, 

Hope someone can help me out. 

Let's say I have a data model with a Store Table and Billings Table such that 

Now, I want to have a scenario where I can have a store name slicer and a store state slicer. When I select a store name, it will give me the sales for the particular store and when I selected a state, it would give me the total amount for the entire state in the same visual. 

For instance, in the below table 

Store 2 should be 410 (120+140+150) while Billings (State) should be 1,050 (summation of store 1, 2 and 3 as they're all part of CA)

Workarounds I've tried: 

Creating two Store tables and then manually using ALLSELECTED where 

Billings = CALCULATE(SUM('Billings Table'[Invoice Amount]),ALLSELECTED('Store Table'[Name]))

Billings (State) = CALCULATE(SUM('Billings Table'[Invoice Amount]),ALLSELECTED('Store Table (2)'[State]))

Unfortunately, it doesn't work the way I want it to as there is no filtering happening. 

[vapid128]

add a relate colnum in Bill table.
State = related(Store Table[State])

Or

Billings (State) = 

var _State = SELECTEDVALUE('Store Table (2)'[State])

CALCULATE(
    SUM('Billings Table'[Invoice Amount]),
    Filter(ALLSELECTED('Billings Table'),RELATED('Store Table (2)'[State]) = _State)
)

And change your relationship to both.

[Imrans123 · Accepted solution]

Was about to delete this post 😂

Realized if we are using two different columns to filter, can just use removeiflters for the column which we don't want to filter. 

i.e. 

Billings (State) = CALCULATE(SUM('Billings Table'[Invoice Amount]),REMOVEFILTERS('Store Table'[Name]))

Whereas the state column from store table will still apply the filter and hence the state slicer will affect the measure. 

Thanks though!