Filtering Clustered Column Chart with Data From Two Tables

[charbaugh]

I have a clustered column chart that is pulling data from two tables - one that is a revenue report and one that is simpy a table that has quotas by brand, month, and region.  The chart diplays fine, but when I try to apply a slicer or filter, the slicer or filter only affects either the quota or the revenue, but not both.  I've setup relationships between all the tables and made sure the column headers are named the same.

[charbaugh]

I have revised my strategy on this a bit but am still getting the similar results.  Instead of a table with the revenues, a table defining the regions, a table defining date criteria (i.e. month, period, etc), and a table with the quotas, in Power Query Editor, I did a merge query a couple of time to combine the revenue, date, and region tables into one.  So now I'm left with only two tables to reference - ACVS2 and 06 FY20 Quotas.  I've tried every combination of the relationship between the two table from both directions to one direction, changing the field the relationship is based on, changing the table from which the slicer or filter field is from, changing the table from which the Axis field [Brand] is from, etc.  Obviously, with each combination there is a slightly different behavior, however no matter the combination I could never get a slicer to change the visualization of data from both tables.  I did try putting a filer/slicer in from both tables, and if I do that I get the results I'm looking for - example, slicer [Region] from ACVS2 and slcier [Region] from 06 FY20 Quotas.  My challenge is getting one slicer to do both tables.

[amitchandak]

FY20 Quota seems to have data at region level And AVC5 Seems to have as state level. You Region Dimension seems to have both state and Region. I doubt there is the correct relation. Can you share the relationship diagram.

[charbaugh]

Certainly.  I meant to include that with the original post.  I'm new to BI and am trudging my way through it.  I figure it is a relationship issue but have not been able to figure it out.  I know one answer would be to insert the Region field into the ACVS table, but I didn't want to create a conditional column with 60+ arguments.  So I created the Region Definition table to be the reference/key.

The relationships below are :

• Date [Ship Date} <-> ACVS [FullDateAlternateKey]:  I'm using this to create the definition for fiscal year, fiscal month, fiscal period, etc
• Region Definition [State] <-> ACVS [State]:  I'm using this to create the definition for the regions
• FY20 Quota [Brand] <-> ACVS [Brand]:  I'm using this to define the quotas by region and brand

I appreciate any help.