Exclude column from map visual filter

[MWinter225]

here is my data:

Location	Store	Division	Sales
NY	1	A	100
NY	1	B	200
NY	1	C	100
NJ	2	A	300
NJ	2	C	100
MA	3	A	600
MA	3	B	300
MA	3	C	100

what i'm trying to do is have a map with pie charts on it, and when i click on a pie chart, the dounut chart on the right will reflect the data in the pie chart on the map. Right now when I'm clicking the 'Division A' slice of the pie chart in MA, it only shows me the data for Division A in MA. I want to see ALL THE DATA FOR THAT MA LOCATION! 

I tried writing the measure SalesExlcudingDivision= CALCULATE( SUM('Sheet1'Sales), ALLEXCEPT('Sheet1','Sheet1'[Location]))

and that will give me ALL the sales data for that location but as soon as I add Division in to the legend it messes it up. Any thoughts?? thanks in advance!

Download the PBIX HERE

[Phil_Seamark · Accepted solution]

Hi @MWinter225

The challenge is, when you make a selection on the pie chart in the map on the left you filter the table making it difficult to generate values needed for the Axis of the donut chart.

One way to solve this is to create a clone of your Sheet1 table and use that for the donut, and your Sheet1 table for the map.

I've mocked up a simple example here

https://1drv.ms/u/s!AtDlC2rep7a-ogs6CO3icHDYmp0J