Exclude rows in a matrix if a Column has the same value at the row total

[balestram]

I have developed a simple matrix like the one blow to report on the status of the receommendations from our audits.  I would like to exclude from the visual those rows where the total of the colomn Closed is equal to the Column Subtotal (i.e. where all audit recommendation are closed and there are none left open).  In my example, I would like to exclude project 5678 as all 10 recommendations are closed and the row total is also 10.  Any suggestion on how to acheive that would be appreciated.  Thank you.

Project Code	Audit Name	Recommendations Open	Recommendations Closed	Total
1234	Audit 1	5	5	10
5678	Audit 2	0	10	10

[balestram]

Maybe this way what I am trying to acheive will be more clear.  This is the table behind the matrix (what would be a pivot table in Excel).  When I do the matrix, Audit 2 should not appear as all recommendations are closed.  I need to figure out a way to add a measure to highlight the recommendations from those projects where all recommendations are closed.  Then I could use that measure as a visual level filter to exclude certain audits.

Project Code	Audit Name	Recommendation Title	Recommendations State
1234	Audit 1	Recommendation 1	Open
1234	Audit 1	Recommendation 2	Closed
1234	Audit 1	Recommendation 3	Closed
1234	Audit 1	Recommendation 4	Open
1234	Audit 1	Recommendation 5	Open
1234	Audit 1	Recommendation 6	Open
1234	Audit 1	Recommendation 7	Closed
1234	Audit 1	Recommendation 8	Open
1234	Audit 1	Recommendation 9	Closed
1234	Audit 1	Recommendation 10	Closed
4567	Audit 2	Recommendation 1	Closed
4567	Audit 2	Recommendation 2	Closed
4567	Audit 2	Recommendation 3	Closed
4567	Audit 2	Recommendation 4	Closed
4567	Audit 2	Recommendation 5	Closed
4567	Audit 2	Recommendation 6	Closed
4567	Audit 2	Recommendation 7	Closed
4567	Audit 2	Recommendation 8	Closed
4567	Audit 2	Recommendation 9	Closed
4567	Audit 2	Recommendation 10	Closed

[selimovd]

Hey @balestram ,

if it's per row you could add a calculated column that checks if they are identical:

CheckTotal = IF( myTable[Recommendations Closed] = myTable[Total], 1, 0)

In the filter pane for the visual you can then filter to consider only the rows where CheckTotal = 0:

I think that should give you the result you want.

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[amitchandak]

@balestram , Create a measure like this and use it in visual level filter

Check for non blank or =1 in the visual level filter

if([Recommendations Open]+[Recommendations Closed] = [Recommendations Closed] , blank(), 1)

[balestram · Accepted solution]

I ended up solving the issue myself.  I created a new table that counts the sum of the closed recommendtions and the sum of the non closec recommendations.  Then it count the total closed, minus the non closed, minus the closed again.  if the result is equal to zero, then that audit needs to be filtered out as all recommendations are closed.  I then join this table to the main table and filter out the rows whene the result of the above subtraction is zero.