How to get the correct Table Total

[roxieli123]

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.&nbspI've attached what I did for 3rd and 4th column below, it will be greatly appreciated if anyone can help.

[roxieli123]

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.&nbspI've attached what I did for 3rd and 4th column below, it will be greatly appreciated if anyone can help.

[Nathaniel_C]

Hi @roxieli123 ,

Please see the picture below, and the measure.

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

Group total = CALCULATE(SUM('AUM DETAIL'[Gross]) ,FILTER('AUM DETAIL','AUM DETAIL'[Account num]))

[Nathaniel_C · Accepted solution]

@roxieli123 ,

Below are my two tables designed from your description, very nice. Then the relationship. And my pbix Groups

Note that I included one account number not assigned to a group.

Please let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

f

l