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 you should set up a relationship between these two tables which I think will be 1 to 1 and then use that logic in the DAX to only include manufacturer that are in the 2nd table.
As I said, SELECTEDVALUE does not give you the same value at non manufacturing rows, it gives you a blank value. Those are two different measures.
- dg1234567892 years agoAdvocate III
parry2k it is not 1:1 because some manufacturers produce products in several categories. I have a relationship between category columns as stated in my original post. So if I'm in the DETERGENT Category, then the list of manufacturers is limited to just those in that category.
So if SELECTEDVALUE() is not the way, and i've tried MAX() as well, how do I do this?