Forum Discussion
Many to Many relationship
Dear Community Members,
My fact table is Indices table and have joined the Market Basket table using many to many relationship . I am trying to achieve average weighted indexvalue by years. Market Basket is a portfolio name which has multiple indices. The Indices table has the Index Value which is the avg. monthly price for all the indices in the Market Basket table . [% of Cost Element] are weights of the index in the portfolio
Would really appreciate any ideas.
Market Basket Table
Model View
You can use TREATAS or CROSSFITER to do the calculation, but avoid using many-to-many relationships.
In this example:
You can use the following to calculate the weight by channel (the common field in both tables is only "Item"
Using TREATAS
Sum weight by channel = CALCULATE ( [Sum Weight], TREATAS ( VALUES ( 'Sales Table'[Item] ), 'Weight Table'[Item] ) )Or using CROSSFILTER
SUM of Weight (CROSSFILTER) = CALCULATE ( [Sum Weight], CROSSFILTER ( 'Sales Table'[Item], 'Dim Item'[Item], BOTH ) )To get:
The choice really depends on the performance of each measure in your model (in my example they perform pretty much the same.
I've attached the sample PBIX file
5 Replies
You should create a dimension table with unique values of the Series ID field covering the range of values present in both tables. Join the Series ID field from the new dimension table to the corresponding fields in both fact tables in one-to-many relationships. You then use the field from the dimension table in your measures, visuals, slicers, filters.....
Paul, this is what I was thinking, however, the users won't select/slice the data by Series ID. Infact the only Slicers available to the users would be [Category] and [Market Basket]. So then with a bridge table how would I get the weights? I need to calculate the average weighted indexvalue by years for each market basket
You can use TREATAS or CROSSFITER to do the calculation, but avoid using many-to-many relationships.
In this example:
You can use the following to calculate the weight by channel (the common field in both tables is only "Item"
Using TREATAS
Sum weight by channel = CALCULATE ( [Sum Weight], TREATAS ( VALUES ( 'Sales Table'[Item] ), 'Weight Table'[Item] ) )Or using CROSSFILTER
SUM of Weight (CROSSFILTER) = CALCULATE ( [Sum Weight], CROSSFILTER ( 'Sales Table'[Item], 'Dim Item'[Item], BOTH ) )To get:
The choice really depends on the performance of each measure in your model (in my example they perform pretty much the same.
I've attached the sample PBIX file