Many to Many relationship Column Access Issue

[Ash187]

Hi, I have two tables, a site specific sales table, Table1, with date, product ID and QtySold (product quantity). The other table, Table2, is an overall sales record table of TotQty (total quanity) per month and year. Here is a snapshot of the data in excel files below

Table1

Date                      ProdID  QtySold   Year

1/4/2018	3	8	2018
1/10/2018	1	17	2018

..

2/5/2018	3	4	2018
2/9/2018	2	16	2018

...

3/1/2019	4	9	2019
3/1/2019	3	4	2019

and the second table, Table2

YearMonthTotQty

2018	January	5000
2018	February	4000
2018	March	7000
2019	January	900
2019	February	800
2019	March	700

Due to the nature of the table, when I connect Year to Year, it's a many to many connection. What I would like to draw is the site specific sales quantity divided by total quantity vs the three months. I am able to view year specific data using a year slicer. Independently the bar charts for site specifc QtySold  and total sales quantity TotQty were easily drawn. But how can I use RELATED function to plot the quantity sold to total quantity ratio ( i.e. the site specific QtySold in Table1 divided by total sales quantity in Table2) vs the three months for that year ? 

[Arjunarao · Accepted solution]

You can achive your requirement like this.
Create a Calendar and design the data model as shown.

Download the file from here: https://drive.google.com/open?id=15DYzOIHn0-uTjCjFKSHGcSVoIUgayAEn

Calendar =
VAR MinYear = YEAR ( MIN ( Table1[Date] ) )
VAR MaxYear = YEAR ( MAX ( Table1[Date] ) )
RETURN
ADDCOLUMNS (
FILTER (
CALENDARAUTO( ),
AND ( YEAR ( [Date] ) >= MinYear, YEAR ( [Date] ) <= MaxYear )
),
"Calendar Year", "CY " & YEAR ( [Date] ),
"Year", YEAR ( [Date] ),
"Month Name", FORMAT ( [Date], "mmmm" ),
"Month Number", MONTH ( [Date] )
)

[Ashish_Mathur]

Hi,

What you should be ideally be doing is this:

1. In Table2, create a Date column by writing the following calculated column formula: Date=1*("1/"&Table2[Month]&"/"&Table2[Year])
2. Create a Calendar Table
3. Build a relationship from the Date column of Table1 and Table2 to the Date column of the Calendar Table
4. In the Calendar Table, write these calculated column formulas to extract Year and Month: Year = Year(Calendar[Date]) and Month = FORMAR(Caloendar[Date],"mmmm")
5. To your visual, drag Year and Month from the Calendar Table

Hope this helps.

[itchyeyeballs]

I'd look at creating a third table with the years in it to act as a common dimension table rather than connect the two fact tables.

This link has good description https://powerpivotpro.com/2012/01/salesbudget-integrating-data-of-different-grains/