Many-to-many connection between tables

[Yura_greenit]

Hello everyone.

I have faced with the following problem::

I have two main tables:
Cash Flow table and Land Bank table

The Cash Flow table shows the expenses on fields by date.

The Land Bank table shows the movement of the land bank (change of field area by date).

The common columns between the tables "Field" and "Actual Crops", but the Land Bank table also has a "Planned Crops" column.

It should be shown Expenses on the date of the planned crops.

I wrote a measure that calculates the expenses per date by the Cash Flow table, and I also count the area of each field on the date by the Land Bank table. There is Many-to-many connection between tables.

How to display expenses in the context of planned crops in one visualization? I would appreciate any help.

[parry2k]

@Yura_greenit can you provide sample data and expected output. Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[Yura_greenit]

Sorry, attaching the attachment file and test a small circuit:

https://mriya-my.sharepoint.com/:u:/g/personal/ypatrash_cfg_com_ua/ERrir2yxatNIpGBM6zNNfTkBACh5gDfrT...

There are actually a lot more tables, but there are two basic ones. The problem is I can't relate them.

[Yura_greenit]

[v-deddai1-msft · Accepted solution]

Hi @Yura_greenit,

Would you please try to create a measure for value:

Value2 =

DIVIDE (

    SUM ( 'Land Bank'[Field area] ),

    CALCULATE (

        SUM ( 'Land Bank'[Field area] ),

        FILTER (

            ALL ( 'Land Bank' ),

            'Land Bank'[Actual Crops] IN DISTINCT ( 'Cash Flow'[Actual Crops] )

        )

    )

)

    * SUM ( 'Cash Flow'[Value] )

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai