Forum Discussion

Artemis1254's avatar
Artemis1254
Frequent Visitor
8 years ago
Solved

Filter out 0/Negative/Null values when calculating Median

Hi, I have a calculation for median that is built from this formula.

 

Median Latency Test =
VAR NumValuesHalved = COUNT ( Query2[Latency] ) / 2
RETURN
MINX (
FILTER (
VALUES ( Query2[Latency]),
CALCULATE (
COUNT ( Query2[Latency]),
Query2[Latency] <= EARLIER ( Query2[Latency] )
)
< NumValuesHalved
),
Query2[Latency]
)

 

I am wondering how to alter this formula to filter out 0/negative/null values so that they are  not utilized in the calculation of Median Latency. Can anyone help with this?

Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    Hi Artemis1254

     

    Give this a try

     

    Median Latency Test =
VAR NumValuesHalved =
    COUNT ( Query2[Latency] ) / 2
RETURN
    MINX (
        FILTER (
            VALUES ( Query2[Latency] ),
            Query2[Latency] > 0
                && CALCULATE (
                    COUNT ( Query2[Latency] ),
                    Query2[Latency] <= EARLIER ( Query2[Latency] )
                )
                    < NumValuesHalved
        ),
        Query2[Latency]
    )

     

     

2 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Icon for Community Champion rankCommunity Champion
    8 years ago

    Hi Artemis1254

     

    Give this a try

     

    Median Latency Test =
VAR NumValuesHalved =
    COUNT ( Query2[Latency] ) / 2
RETURN
    MINX (
        FILTER (
            VALUES ( Query2[Latency] ),
            Query2[Latency] > 0
                && CALCULATE (
                    COUNT ( Query2[Latency] ),
                    Query2[Latency] <= EARLIER ( Query2[Latency] )
                )
                    < NumValuesHalved
        ),
        Query2[Latency]
    )