Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Relationship and Filtering in Table Visualization

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. 

 

 

 

jfarver_0-1601569965472.png

 

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

 

5 REPLIES 5
jdbuchanan71
Super User
Super User

@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 

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

Anonymous
Not applicable

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. 

 

 

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

 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors