- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Sorry, attaching the attachment file and test a small circuit:
There are actually a lot more tables, but there are two basic ones. The problem is I can't relate them.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
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.
Subject | Author | Posted | |
---|---|---|---|
06-20-2024 07:22 PM | |||
06-26-2024 04:33 AM | |||
06-27-2024 02:56 PM | |||
07-26-2024 07:45 AM | |||
06-24-2024 01:54 AM |
User | Count |
---|---|
115 | |
75 | |
45 | |
44 | |
32 |
User | Count |
---|---|
172 | |
90 | |
66 | |
46 | |
45 |