Forum Discussion
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.
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
6 Replies
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.⚡
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
What do you mean "add that in the measure"??
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.
Thanks for the tip, I guess I haven't used crossfilter in a measure before.