Forum Discussion
DAX to Calculate Sum Ignoring a Column
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:
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 ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
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 ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
It works perfect. Thank you very much, Rico!
Hi,
Share the download link of the PBI file.
