I have 3 tables: Budget, OS Acct Master, Acct Hierarchy, Actual. The relationship is below. Note there can be multiple ERP Accts to 1 OS Acct (which I think is what's causing the issue I'm seeing). Budget is based on OS Acct and Actual is based on ERP Acct, which are not the same granularity.
I want the Budget Amt to break down to the individual levels based on the OS Acct in a table. However when I do this, it sums up for the entire Level 1. This is what PBI gives me:
Actual is great, but Budget is summing up for the whole Level 1.
Here is what I'm hoping to get:
Here is is a screenshot of the data in the tables. I have a sample pbix but can't figure out where to add attachments.
How can I setup the relationship between Acct Hierarchy and Budget better to make this aggregate through the levels properly?
I actually took out the OS Acct Master table because it didnt' seem to actually be doing anything besides causing ambiguity. Now I have a many to many, but it works.
@smileamile2 well in a larger model it is not recommended, it is a weak relationship, you should have tried changing the cross filter to both, would have worked. anyhow, just a recommendation.
If cross filter direction both worked, then you just simply added that in the measure and change the direction back to single, you would have a better data model rather these many to many
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.
@smileamile2 follow the first step first (if you read my first reply), and then you can create a measure to enable cross filter direction to both.
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.
@smileamile2 well in a larger model it is not recommended, it is a weak relationship, you should have tried changing the cross filter to both, would have worked. anyhow, just a recommendation.
If cross filter direction both worked, then you just simply added that in the measure and change the direction back to single, you would have a better data model rather these many to many
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.
I actually took out the OS Acct Master table because it didnt' seem to actually be doing anything besides causing ambiguity. Now I have a many to many, but it works.
@smileamile2 there are two ways to solve it, change the cross filter direction between account and os account master to both by clicking the relationship link, it is not a recommended method, or you can add a measure and make this relationship both. first test by changing the cross filter to both to see if it works.
Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
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.
Helpful resources
New to Fabric Survey
If you have recently started exploring Fabric, we'd love to hear how it's going. Your feedback can help with product improvements.
Power BI DataViz World Championships - June 2026
A new Power BI DataViz World Championship is coming this June! Don't miss out on submitting your entry.
Join our Fabric User Panel
Share feedback directly with Fabric product managers, participate in targeted research studies and influence the Fabric roadmap.
Power BI Community Update - March 2026
Check out the March 2026 Power BI update to learn about new features.
