cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
roxieli123
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
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors