Relationship and Filtering in Table Visualization

[Anonymous]

I have multiple tables I am pulling the information from, but the main two I am having an issue with are the product table and the product_product table. My company uses the product table as main categories, and product_product as sub categories. I'm attempting to display the sub categories and count them and sum the total price in each category. Four of the sub categories share the same category id, causing them to 'double count' in my display. 

The categories should show as: 

Flood - Life of Loan - 56 - $28.00

Property Condition Report - 21 - $21.00

Title - C/O Update - 41 - $328.00

e-Recording - 46 - $9.20

AVM - 59 - $59.00

A secondary issue I have is the 'Grand Total' doesn't match the sum of all totals. Based on the clip below, the Total should show as 3922.20 not 22,677.20. I have no idea where that number is coming from or how it is being displayed that way. My company would like an at a glance Grand total to show for an accounts payable report. 

Please let me know if more information is needed, I struggled figuring out how to ask what I am looking for! 

[jdbuchanan71]

@Anonymous 

Can you tell us how the [Sum Total] column is calculatated?  What is the formula that gives you 830 on the first line?  Would it be possible for youto share your .pbix file with us? (upload it to OneDrive or DropBox and share the link).

It helps a lot to be able to see the table / field names and relationships you have defined in your model.

The short answer for the total is it is getting 22,677.20 because I am guessing it is multiplying the Quantity of 356 by 63.7 but again, we need to see the formula for [Sum Total].

Take a look at this post to help guide you to the sort of information that helps us answer your questions.  https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[Anonymous]

https://lenderclose-my.sharepoint.com/:u:/p/jfarver/ESdckH1hu7tBo-gfdTrO3kIBBoKis-T65CHRvhAZK4_K4g?e...

[jdbuchanan71]

@Anonymous 

I won't have access to your company sharepoint site so I cannot download it from there.  

[Anonymous]

The [Sum Total] is a quick measure calculation of [Price] x [Sum Quantity] 

I'm new to BI and still learning tricks and the correct way to measure or include neccessary information, before starting at my company I was a big Excel kind of person and there's a vast difference in how some of these matters are achieved I'm quickly learning. 

[jdbuchanan71]

The total row is doing what you asked it to do, where you are getting stuck is understanding the behavior.

For each line it is summing the QTY and summing the Price then multiplying them together.

For the total row it is getting the sum of all qty and the sum of all prices then multiplying them.  

A total row has no concept of the rows above it, they play no part in how it calculates.

What you need is a SUMX measure that will calc the amount then add it up like so (you will need to change the table name to match your model).

Total = SUMX ( YourTable, YourTable[Price] * YourTable[Quantity] )