DAX to Calculate Sum Ignoring a Column

[DataSundowner]

Hello Community. I need some help to build a DAX measure that calculates sum of sales for total region regardless of store being filtered. It sounds pretty straight forward, but my model is a little bit tricky since I have a Store Map table that has multiple map styles for each region, and I need the relationship between the Map table and Store table to be bi-directional so they can filter each other. I've tried many different ways, but yet to find a final solution that returns the results I want. I can't seem to attach the sample pbix file here, but below are the screenshots of data and model. Thank you in advance. 

Model:

Data 1: Sales

Data 2: Store

Data 3: RegionStoreMap

Report View:

Current Measure:

Desired Results: 

[Anonymous · Accepted solution]

Hi @DataSundowner ,

I suggest you to try code as below to create a measure.

Sales - Region Total = 
CALCULATE(SUM(Sales[Sales]),REMOVEFILTERS(RegionStoreMap[MapStoreID]),Store[RegionID] in VALUES(Store[RegionID]))

Relationship in my sample:

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DataSundowner]

It works perfect. Thank you very much, Rico! 

[Ashish_Mathur]

Hi,

Share the download link of the PBI file.