cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Ash187 Visitor
Visitor

Many to Many relationship Column Access Issue

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/2018382018
1/10/20181172018

..

2/5/2018342018
2/9/20182162018

...

3/1/2019492019
3/1/2019342019

 

and the second table, Table2

YearMonthTotQty

2018January5000
2018February4000
2018March7000
2019January900
2019February800
2019March700

 

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 ? 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Arjunarao Regular Visitor
Regular Visitor

Re: Many to Many relationship Column Access Issue

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] )
)

1.JPG

2.JPG

 

3 REPLIES 3
itchyeyeballs Established Member
Established Member

Re: Many to Many relationship Column Access Issue

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/
Super User
Super User

Re: Many to Many relationship Column Access Issue

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.

Arjunarao Regular Visitor
Regular Visitor

Re: Many to Many relationship Column Access Issue

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] )
)

1.JPG

2.JPG