Getting an amount when the two tables don't have a relationship

[brs09j]

So I have tables A, B and C

Table A is the fact table and table B is related to table A in a One-to-many relationship.

Table B is related to table C in a One-to-many relationship as well.

Table A and Table C have no direct relationship. However, they are both related to table B.

I want the amount from the cost column in table A to go under the product description column from table C in a matrix.

I wrote the following below but I'm not sure it is correct or even needed. Do I just need to switch my relationship filtering settings? Joining table A to table C would be many to many. Is that even allowed in Power BI?

Sales Amount =
CALCULATE (
    SUMX ( Table_A, Table_A[Cost] * Table_A[Quantity] ), 
    Table_C
)

Thanks.

[Greg_Deckler]

In theory, you should not need that calculation since it is a simple SUM essentially. You can have many-to-many in Power BI with a bridge table as you have in your model. I'd have to model out what you are doing to be certain, can you supply sample data?

[brs09j]

@Greg_Deckler

I'm sorry, I don't have any generic sample data. You say that it is a normal SUM since the amount is already stored on Table A and no calculation is needed. That part makes sense to me, but since there is no relationship I can't use RELATED or RELATEDTABLE.

[brs09j]

@Greg_Deckler

To check,

I created a calculated column on table B

Transaction Amount = 
   SUMX (
        RELATEDTABLE ( Table_A) , Table_A[Expenses] * 1)

I get unique values here for my new column but when I drag the new field into the matrix I get the same number repeating

[brs09j]

@Greg_Deckler

I made the expenses a row in the matrix visualization instead of a value and that gave me the unique numbers I needed. Any idea why it works that way?

Thanks.

[Greg_Deckler · Accepted solution]

It's going to be dependent on the cross-filter directions of your relationships. If Table A filters your bridge table, Table B and Table B filters Table C then you have a many-to-many relationship in your case or vice versa. I'd have to see the relationship map.