cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
roxieli123 Frequent Visitor
Frequent Visitor

How to get the correct Table Total

 
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.

 

2.png22.png

1 ACCEPTED SOLUTION

Accepted Solutions
Super User IV
Super User IV

Re: How to get the correct Table Total

@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

 

 

 

Group names1.PNGf

 

Group names2.PNGl

 

Group names3.PNG





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

2 REPLIES 2
Super User IV
Super User IV

Re: How to get the correct Table Total

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]))

 

Group names.PNG





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Super User IV
Super User IV

Re: How to get the correct Table Total

@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

 

 

 

Group names1.PNGf

 

Group names2.PNGl

 

Group names3.PNG





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors