Many to Many Relationship

[fluppo]

Dear community

I try to split a statement of accounts to functions ... and got completely stuck.

I have two tables with a many-2-many relationship. I did set up a bridge table to get the relationship generally to work.

Total Expenses =sumx(PL;PL[Amount])

Total Expense by Function =CALCULATE([Total Expenses];Functions)

The second measure provides the expenses for the functions involved, but I just don't know how to multiply the values of the two tables in order to have the sum of the expenses by function matching the total expenses.

(I would be happy to attach a workbook with the tables, just don't know how.)

I would really appreciate if someone could point me in the right direction.

Many thanks in advance

F.

[Anonymous]

Hi @fluppo ,

You can post the data, pbix onto OneDrive / GoogleDrive and share the link here. Please also let know the ouput you expect.

Cheers

CheenuSing 

[fluppo]

Hi CheenuSing

Here is the link to the file:

https://1drv.ms/x/s!AqSp6PyLa1qviwHoClkCribvQdUG

Best regards

F.

[fluppo]

I managed to find a possible solution based on the post from Cherry:

/Multiply-two-tables-with-a-many-to-many-relationship

However, I don't really understand the solution and would appreciate if you could help me with the following question:

The measure creates a table on the fly, right. Is this an efficient solution? When I apply it to a mid-sized set of data it is calculting for quite some time (I might be too impatinent though)

Are there alternatives methods?

Best regards

F.

[christianfcbmx]

@fluppo if you dont have sensitive data, send me your pbix to check it out through www.wetransfer.com

my email is dominguezcampusano@gmail.com