Many-to-Many relationship with multiplication between tables

[jenny99]

I have a model with a many to many relationship.  In my example below egg is in two categories (Dairy and Protein).  I would like to incorporate the "Multiplier" column in the Bridge Table below to make eggs/protein a negative amount in the output, but have the eggs/dairy combination be positive.  All necessary relationships are set up in my data model.

Food

Egg

Bread

Milk

Category

Dairy

Protein

Grains

Food_Category_Bridge

Food	Category	Multiplier
Egg	Dairy	1
Egg	Protein	-1
Bread	Grains	1
Milk	Dairy	1

Fact_Table

Food	Sales
Egg	5
Bread	5
Milk	5

Measures:

TotalSales = SUM(Fact_Table[Sales])

TotalSales_ManyToMany = CALCULATE([TotalSales],Food_Category_Bridge)

Desired Output:

Food	Category	Sales
Egg	Dairy	5
Egg	Protein	-5 (currently Positive)
Bread	Grains	5
Milk	Dairy	5

My current output has a positive 5 on second row, I would like it to be negative by multiplying by the multiplier column in the bridge table above.  Any help on how to modify my existing measures to accomplish this is greatly appreciated.

[jenny99]

Your solution worked very well for my simple example.  My actual model is a bit different.

My measure returns Blanks using: 

* SELECTEDVALUE ( Food_Category[Multiplier] )

It returns values (but wrong) if I use:

* SELECTEDVALUE ( Food_Category[Multiplier],1)

In my model, I have 4 Fact tables with a many to many relationship similar to the one shown.  All four bridge tables are related to a the Category_Description table as shown below.  In my reports, I am not showing the food, only category.  I add the four measures from each fact table to get the total and everything works fine.  The only problem is if I create a matrix from the Category_Description table below, the SelectedValue from your measure returns a blank.  If I create a Pivot table with the single measure from the fact table and drag the food and category tables to the row fields on a matrix, it works perfectly.  I just need to find a way to get the multiplication to work when using the table without the multiplication values.  I have tried RELATED and other things, but have been stuck.

Category_Description(Related to all 4 Bridge tables)

Category	Description
Grains	xyz Grains
Dairy	xyz Dairy

[parry2k · Accepted solution]

@jenny99 Assuming you have the following relationship

Add this measure:

Sum Sales = 
CALCULATE ( 
    SUM ( 'Fact'[Sales] ),
    CROSSFILTER ( Food[Food], Food_Category[Food], Both ),
    CROSSFILTER ( Category[Category], Food_Category[Category], Both )
) 

* SELECTEDVALUE ( Food_Category[Multiplier] ) 

and here is the output, using the Food and Category column from the respective table (not from the bridge table)

✨ Follow us on LinkedIn

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡