How to filter out and calculate the values ?

[Anonymous]

I am currently using this formula to calculate a value,

Reslnc_F_18 - 29 = if('D Star Survey'[Age_group] = "18 - 29",if('D Star Survey'[Gender] = "Female",if(max('D Star Survey'[Observation Date]),if('D Star Survey'[phases] = "Phase-1",if('D Star Survey'[Survey Score - Copy] >PERCENTILE.INC('D Star Survey'[Survey Score - Copy],0.25)&& 'D Star Survey'[Survey Score - Copy]<PERCENTILE.INC('D Star Survey'[Survey Score - Copy],0.75),"Moderate",if('D Star Survey'[Survey Score - Copy] <=PERCENTILE.INC('D Star Survey'[Survey Score - Copy],0.25),"Low",if('D Star Survey'[Survey Score - Copy]>=PERCENTILE.INC('D Star Survey'[Survey Score - Copy],0.75),"High","")))))))

Now having this condition in place this formula gives values for only those who are in Phase - 1,what I want to do is to be able to see those who are in phase -2 as well ,

something like if they are in phase -1 do the ,Low,Moderate and High for that population.If they are in phase -2 ,do that Low,Moderate ,High for that population.

Now how do I bring this here?

Thanks

[d_gosbell · Accepted solution]

I hate to be the bearer of bad news, but I'm assuming from the syntax that this is a calculated column. Therefore the PERCENTILE.INC function is calculating over the entire column, not just for 18-29 females in Phase 1. If you want to calculate over a limited population in a calculated column you should probably use PERCENTILEX.INC over a filtered set of data.

You could do something like the following to capture the phase of the current row and then calculate the percentile within that population. (note I'm also using variables to avoid repeatedly calculating the percentile values)

eg.

Reslnc_F_18 - 29 =
VAR _currentRowPhase = 'D Star Survey'[phases]
VAR _25Percentile =
    PERCENTILEX.INC (
        FILTER (
            'D Star Survey',
            'D Star Survey'[phases] = _currentRowPhase
                && 'D Star Survey'[Age_group] = "18 - 29"
                && 'D Star Survey'[Gender] = "Female"
        ),
        'D Star Survey'[Survey Score - Copy],
        0.25
    )
VAR _75Percentile =
    PERCENTILEX.INC (
        FILTER (
            'D Star Survey',
            'D Star Survey'[phases] = _currentRowPhase
                && 'D Star Survey'[Age_group] = "18 - 29"
                && 'D Star Survey'[Gender] = "Female"
        ),
        'D Star Survey'[Survey Score - Copy],
        0.75
    )
RETURN
    IF (
        'D Star Survey'[Age_group] = "18 - 29",
        IF (
            'D Star Survey'[Gender] = "Female",
            IF (
                MAX ( 'D Star Survey'[Observation Date] ),
                IF (
                    'D Star Survey'[Survey Score - Copy] > _25percentile
                        && 'D Star Survey'[Survey Score - Copy] < _75percentile,
                    "Moderate",
                    IF (
                        'D Star Survey'[Survey Score - Copy] <= _25percentile,
                        "Low",
                        IF ( 'D Star Survey'[Survey Score - Copy] >= _75percentile, "High", "" )
                    )
                )
            )
        )
    )

[Anonymous]

Thanks a lot.This really worked well @d_gosbell