Forum Discussion
Filtering matrix rows by a column value
I've solved this with the help of Phil_Seamark comment above. Instead of using the bucket field in the columns section of the matrix, I've created a measure for each bucket using the below formulas and put that in the values:
Current = calculate(sum(Table1[$Amount]),Table1[Bucket]="Current")
30= calculate(sum(Table1[$Amount]),Table1[Bucket]="30")
etc...
Then used the Has 90 day Value filter, Has 30 day Value filter, etc. and created a tab for each bucket filter: Current filter, 30 filter, etc.
I had some success by creating the following measure :
Has 90 day Value = CALCULATE(
SUM('Table1'[Amount]),
ALLEXCEPT(
Table1,
Table1[Customer Name]
),
Table1[Bucket]="90")But not adding it to the matrix. I just added it as a Visual level filter as below
hi Phil_Seamark,
The only thing I would like to be able to do at this point is to include that measure to the report as a slicer for the end user to use instead of having to do it "behind the scenes". This gets me the closest I have been so far though and I can create separate tabs for each filter option until a different solution comes along. Thank you for your help!
Well, after talking with the end user, the other thing I need is to be able to sort by a column in a matrix...I've seen a lot of forum posts about this and have voted for ideas on this so I'll just wait until it's something that is available to us.
I've solved this with the help of Phil_Seamark comment above. Instead of using the bucket field in the columns section of the matrix, I've created a measure for each bucket using the below formulas and put that in the values:
Current = calculate(sum(Table1[$Amount]),Table1[Bucket]="Current")
30= calculate(sum(Table1[$Amount]),Table1[Bucket]="30")
etc...
Then used the Has 90 day Value filter, Has 30 day Value filter, etc. and created a tab for each bucket filter: Current filter, 30 filter, etc.