Forum Discussion
Limiting Rows in Matrix Table is not Subtotaling
- 2 years ago
This was the closest answer to get me to figure it out.
The actual answer is
VAR included_manufacturer = if( AND( SELECTEDVALUE('Product'[Manufacturer]) in VALUES('Category Competing Manufacturers'[Manufacturer]), SELECTEDVALUE('Product'[Category]) IN VALUES('Category Competing Manufacturers'[Category])) , 1 , BLANK() ) RETURN IF( sumx('Product',[included_manufacturer]) > 0 , [Total Dollar Sales] , BLANK() )But yes, the trick is for it to roll up to the subtotals we need it to be a sum, or some calculation that allows the subtotal lines to still calculate their totals.
So this final formula gives the included manufacturers a value of 1 and all other manufacturers a blank value. Then anywhere the sum of the included manufacturers is greater than 0 we can insert the Total Dollar Sales, so this means that the logic statement is TRUE for both the included manufacturers as well as the higher subtotal levels that could have sums of 2 or more.
dg123456789 I see, maybe it will be easier if you put sample data in Excel file, you don't need full blown model, just relevant tables to this particular question.
- product table
- category manufacture table
and I guess there will be a fact table that calculates the dollar amount and has a relationship with the product table on the product id.
And the relationship between these tables.
- dg1234567892 years agoAdvocate III
I have a sample pbix file that I've created, but I cannot attach it here, and as I am on a work machine the option I have is a github repo to host a file publically. Can you see it here: dghenkel/subtotal_test (github.com)