Reply
Yura_greenit
Frequent Visitor
Partially syndicated - Outbound

Many-to-many connection between tables

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.

1 ACCEPTED SOLUTION

Syndicated - Outbound

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] )

Untitled picture5.png

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

View solution in original post

4 REPLIES 4
parry2k
Super User
Super User

Syndicated - Outbound

@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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Syndicated - Outbound

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.

Syndicated - Outbound

Без імені.png

Syndicated - Outbound

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] )

Untitled picture5.png

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

avatar user

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric Community.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)