Show only those Phase with non zero completion percentage

[Anonymous]

In this matrix, I have Phase column, a Milestone column, Completion percentage column
A phase can have multiple milestones.
I want show only those phase with non zero value.
Scenario 1: Suppose in a phase, some milestone are completed & some milestone are zero not even started but phase got some value due to some milestone are completed. So that milestone must be also in shown within that phase.
How can I achieve it.

Please find the data link:https://docs.google.com/spreadsheets/d/1IFZbgIme6CfffikWvb9ihUGZIXsbnAiptGqWJctkEjk/edit?usp=sharing

Thanks,
mohit
 
Required Result:

[Anonymous · Accepted solution]

Hi @Anonymous 

You can build a filter measure to achieve your goal.

I use your sample to have a test. I add two columns in your sample MonthName and MonthNo.

MonthName = FORMAT('Table'[Planned End],"MMMM")

MonthNo = MONTH('Table'[Planned End])

Measure:

Measure = 
VAR _AVG = CALCULATE(AVERAGE('Table'[%Completion]),ALLEXCEPT('Table','Table'[Phase],'Table'[MonthName]))
RETURN
IF(_AVG = 0,1,2)

Build a matrix like yours and add this measure into filter field. Set it to show items when value equal to 2. Result is as below.

Best Regards,

Rico Zhou

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