Exclude Zeros and Blanks from MIN

[Anonymous]

I created a measure to give me the bottom third and I don't want to include Zero or Blank rows because it makes the calculation incorrect.

Current Measure:

CALCULATE(

(MAX (JiraEpic[StoryPoints]) - MIN (JiraEpic[StoryPoints]))/3 + MIN (JiraEpic[StoryPoints]),

ALLEXCEPT (JiraEpic, JiraEpic[ScrumTeamName])

)

If I add 

FILTER(JiraEpic,JiraEpic[StoryPoints]<>0 || JiraEpic[StoryPoints]<>BLANK()),

It breaks the measure and gives me a different min for each row instead of the overall for a scrum team.

See Example:   I want the measure to be (104-5)/3 + 5=38 ,  However I get (104-0)/3 + 0=35

Story Points
0
0
5
0
5
49
26
6
104
44

[Vvelarde]

@Anonymous 

Hi, try with this:

Result =
VAR _table_without_0_and_blanks =
    FILTER (
        ALLEXCEPT ( 'Table', 'Table'[Team] ),
        'Table'[Story Points] <> 0
            || 'Table'[Story Points] <> BLANK ()
    )
VAR _Min =
    CALCULATE ( MIN ( 'Table'[Story Points] ), _table_without_0_and_blanks )
VAR _Max =
    CALCULATE ( MAX ( 'Table'[Story Points] ), _table_without_0_and_blanks )
RETURN
    DIVIDE ( _Max - _Min, 3 ) + _Min

Regards

Victor

[amitchandak]

@Anonymous ,

Try like

FILTER(JiraEpic,JiraEpic[StoryPoints]<>0 && not(isblank(JiraEpic[StoryPoints])))

Or

CALCULATE(
(MAXX (FILTER(JiraEpic,JiraEpic[StoryPoints]<>0 && not(isblank(JiraEpic[StoryPoints]))),JiraEpic[StoryPoints]) - MINX (FILTER(JiraEpic,JiraEpic[StoryPoints]<>0 && not(isblank(JiraEpic[StoryPoints]))),JiraEpic[StoryPoints]))/3 + MINX (FILTER(JiraEpic,JiraEpic[StoryPoints]<>0 && not(isblank(JiraEpic[StoryPoints]))),JiraEpic[StoryPoints]),
ALLEXCEPT (JiraEpic, JiraEpic[ScrumTeamName])
)

[az38 · Accepted solution]

Hi @Anonymous 

try

=
var _minVal = CALCULATE(MIN (JiraEpic[StoryPoints]), ALLEXCEPT (JiraEpic, JiraEpic[ScrumTeamName]), JiraEpic[StoryPoints]<>0, NOT(ISBLANK(JiraEpic[StoryPoints])) )

RETURN

(
CALCULATE(MAX (JiraEpic[StoryPoints]), ALLEXCEPT (JiraEpic, JiraEpic[ScrumTeamName]), JiraEpic[StoryPoints]<>0, NOT(ISBLANK(JiraEpic[StoryPoints])) )
-
_minVal 
) / 3
+
_minVal