Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
smileamile2
Resolver I
Resolver I

Relationship causing aggregation at highest level

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.

smileamile2_4-1624402588474.png

 

 

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.

smileamile2_5-1624402672134.png

Here is what I'm hoping to get:

smileamile2_6-1624402816894.png

 

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.

smileamile2_7-1624402843536.png

 

How can I setup the relationship between Acct Hierarchy and Budget better to make this aggregate through the levels properly?

2 ACCEPTED SOLUTIONS
smileamile2
Resolver I
Resolver I

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_0-1624406081198.pngsmileamile2_1-1624406095127.png

 

View solution in original post

parry2k
Super User
Super User

@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.

View solution in original post

6 REPLIES 6
parry2k
Super User
Super User

@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.

Thanks for the tip, I guess I haven't used crossfilter in a measure before.

parry2k
Super User
Super User

@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.

What do you mean "add that in the measure"??

smileamile2
Resolver I
Resolver I

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_0-1624406081198.pngsmileamile2_1-1624406095127.png

 

parry2k
Super User
Super User

@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

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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