Can I create a column demonstrating change between two tables?

[APeterson2014]

Hey all,

I'm having some trouble in demonstrating change between a filtered value and the unfiltered value, and I want to show the changes by using 2 tables (both using the same data source):

Table 1 shows the average sales by year (lets call this X). It gets the average by adding the sales of 3 individuals' values and dividing by 3, so the value is a measure.

Then I have some slicers below that show each sales person's name. These slicers do not apply to Table 1, but only apply to table 2.

Table 2 shows the same sales values but filtered by each slicer (lets call this Y). 

I'm trying to get a new measure of the filtered value minus the unfiltered value (Y - X), preferably in the column next to the filtered value in Table 2. If there's a way to demonstrate the change by using a card or some other visual, that would also work, but I'd prefer it to just be a value in a column in table 2.

I've attached a sample I did in Excel. Any help would be EXTREMELY appreciated!

Thanks 

The highlighted column is what I'm trying to produce as Y-X or filtered value minus unfiltered value. If there were no filters selected it should be 0 each time.

@amitchandak @lbendlin @parry2k @Greg_Deckler

[TomMartens]

Hey @APeterson2014 ,

provide the structure of table 2, this is important to understand how the "filtering" is working. Consider uploading the Excel file you created to onedrive, google drive, or dropbox.

Regards,

Tom

[APeterson2014]

Hey @TomMartens ,

I'm actually working with some sensitive data that I can't share and the excel sheet was just a template to show an example.

Both tables are created using the Import feature of PowerQuery from a SQL server and are essentially duplicates. I added a slicer to only effect Table 2, and am trying to add columns that demonstrate the change.

So "Sales" for year 2021 starts out as 50,000 in both tables, with the new column being +0.

When I click the slicer button to filter sales for table 2, it shows 30,000, with the new column being -20,000.

Hopefully this helps.