Selecting Each Value Returns Different Total Than "Select All" - Total Larger than Sum of Each Row

[SMBM]

I have 3 tables set up like pictured below.  There is a Category table that has a list of material #s that have been put in to certain categories.  It is a subset of the total portfolio, so it only has 17k values in it.  It has a bi-directional relationship with a Materials table which has the entire portfolio, which is just over 5MM unique values.  That Materials table has a one-way filter relationship with the Transactions table which is every transaction of the subset of the 5MM materials that have actually sold.

When I create a table visual that has the AttributeValue off of the Category table and add the Sum of Revenue from the Transactions table, the Sum of Revenue for each AttributeValue is correct, but the total at the bottom of the table visual is for the entire Transactions table, not just the ones that have an AttributeValue from the Category table.  Furthermore, if I select each possible AttributeValue from a filter or slicer, then my table visual total is correct, but if I click "Select All" or select nothing, then it goes back to having the wrong value.  

It should be noted that the Sum of Revenue is NOT a measure, it is an aggregation of a column.  That said, I have tried with measures as well and have the same issue.

I'm guessing I have something set up wrong somewhere, but I can't tell what.  Why doesn't the table visual total only show the Sum of Revenue for all materials that have an AttributeValue from the Category table?  Thank you for any assistance.

[v-xinruzhu-msft]

Hi,

Thanks for the solution audreygerred offered, and i want to offer some more information for user to refer to.

hello @SMBM , based on your description, you may consider to change the filter direction between Category table and Materials table from both to single, if you need to use the both filter, you can use it in dax by using crossfilter() function. You can refer to the following link about the function.

CROSSFILTER function - DAX | Microsoft Learn

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[audreygerred]

In Power Query, can you add a column to your Material Table that designates if that field is part of the subset? You can merge Category and Material tables and just add in one column that will desgnate that that row in Materials is part of the subset in Category. Then, diable load on Category table so that it does not appear in Power BI desktop. Then in your visual, grab your field from Materials and filter it using whatever field you added from Category table so that it is only showing those items. 

Bi-directional relationships can cause unexpected results so you should try to get rid of them whereever possible. 

[SMBM]

I can do that and it is essentially the workaround that I'm using now.  It isn't intuitive for the end user, though.  I would like to use the category as a Field Parameter, but what happens now is that if the select that field for the table, then it gives an accurate revenue value of each category, but the total is an inflated amount.  In the example above, the difference is large enough to quickly realize that it doesn't add up, but in some conditions, it isn't that much different, so the user would expect that the total of the table is the sum of the values in said table.  If there were a "blank" category that the difference went to, that would be one thing, but as it stands, the extra revenue is unaccounted for.

[v-xinruzhu-msft · Accepted solution]

Hi @SMBM 

You can try to create the following measures.

sumrevenue =
CALCULATE ( SUM ( 'Table'[REVENUE] ) )

Totalsum =
SUMX ( VALUES ( 'Category'[Attribute] ), [sumrevenue] )

Then put the totalsum measure to the matrix.

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.