I've spent 3 hours on this simple calculation but I just couldn't get it straight.
So I have two tables,
one is 'DeVore referrals", Which has columns [Group name] and [account Number], so this table tells us the relationship between [account number] and [Group name], let us know which accounts belong to which group.
Another is "AUM_Detail", which has [Account number], [Gross Market value] and [TimeDateID]
two tables are linked by [Account Number], with a ; one(DeVore referral)-to-many(AUM_Detail) relationship ;
I want to calculate each group's total GrossMarketValue for 20190831, in the below table, second column is straight up dragged the [Gross Market Value} field into the table, as we can see the bottom row total is not correct. 3rd and 4th column are measures I wrote to try to get the correct total, however they are also wrong.
The reason I get 40 million is because, the total is adding up [gross market value] for every single [account number], when in this case, not all account number belongs to a group. hence we only want to sum up the ones that belong to a group, I was hoping to do it by using summarize, see the last screenshot down below, however it doesnt work. I've attached what I did for 3rd and 4th column below, it will be greatly appreciated if anyone can help.