Percentile over a measure

[Anonymous]

I used the following formula to calculate the  percentile 0.25 over a measure:   

    Limite Cuartil 1 = CALCULATE(PERCENTILEX.INC('Datos Finca',[Unid x M2 12M],0,25),ALL('Datos Finca'[Sede]))

The value  it returns it´s not the value of the percentile on that category of "AÑO MES", the steps to calculate "Unid x M2 12M" are these:

Unidades = SUM('Datos Finca'[Unid])

Unidades 12M = CALCULATE([Unidades],DATESINPERIOD(Calendario[FECHA],LASTDATE(Calendario[FECHA]),(-1),YEAR))

Area Prom 12M = CALCULATE(AVERAGE('Datos Finca'[Area            Prod]),DATESINPERIOD(Calendario[FECHA],LASTDATE(Calendario[FECHA]),(-1),YEAR)) 

Unid x M2 12M = [Unidades 12M]/[Area Prom 12M]

I attached the orignial data

Percentile 0.25 result

The metrics used to calculate Unid x M2 12M

Metrics calculation

 The result of the percentile, Unid x M2 12M ranges from 82,95 to 125,08 so clearly 5,75 it is not the percentile 0,25

 Thanks

[v-lid-msft]

Hi @Anonymous ,

We can try to create a measure use following formula to meet  your requirement:

Limite Cuartil 1 =
VAR t =
    SUMMARIZECOLUMNS (
        'Datos Finca'[AÑO MES],
        "M12Value", CALCULATE (
            SUM ( 'Datos Finca'[Unid] ),
            DATESINPERIOD (
                Calendario[FECHA],
                LASTDATE ( Calendario[FECHA] ),
                ( -1 ),
                YEAR
            )
        )
    )
VAR ti =
    ADDCOLUMNS ( t, "index", RANKX ( t, [M12Value],, ASC ) )
RETURN
    MAXX (
        FILTER ( ti, [index] = ROUND ( COUNTROWS ( t ) * 0.25, 0 ) ),
        [M12Value]
    )

If it doesn't meet your requirement, kindly share your sample data and expected result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-lid-msft  thank for the help.

The measure was not working on the report so here is the link of the data where on the sheet "Expected Result" it is the table that I am trying to create, a clasification by percentile using the measure "Unid x M2 12M". Hope you can help me.

[v-lid-msft · Accepted solution]

Hi @Anonymous ,

Sorry for that I mixed up the measure and Unidades 12M and Unid x M2 12M , we can use the following measure to meet your requirement:

Limite Cuartil 1 = 
VAR t =
    ADDCOLUMNS (
        GROUPBY (
            FILTER (
                ALLSELECTED ( 'Datos Finca' ),
                'Datos Finca'[AñoMes] IN FILTERS ( 'Datos Finca'[AñoMes] )
            ),
            'Datos Finca'[Sede]
        ),
        "M12Value", [Unid x M2 12M]
    )
VAR ti =
    ADDCOLUMNS ( t, "index", RANKX ( t, [M12Value],, ASC ) )
RETURN
    MAXX (
        FILTER ( ti, [index] = ROUND ( COUNTROWS ( t ) * 0.25, 0 ) ),
        [M12Value]
    )

Limite Cuartil 2 = 
VAR t =
    ADDCOLUMNS (
        GROUPBY (
            FILTER (
                ALLSELECTED ( 'Datos Finca' ),
                'Datos Finca'[AñoMes] IN FILTERS ( 'Datos Finca'[AñoMes] )
            ),
            'Datos Finca'[Sede]
        ),
        "M12Value", [Unid x M2 12M]
    )
VAR ti =
    ADDCOLUMNS ( t, "index", RANKX ( t, [M12Value],, ASC ) )
RETURN
    MAXX (
        FILTER ( ti, [index] = ROUND ( COUNTROWS ( t ) * 0.5, 0 ) ),
        [M12Value]
    )

Limite Cuartil 3 = 
VAR t =
    ADDCOLUMNS (
        GROUPBY (
            FILTER (
                ALLSELECTED ( 'Datos Finca' ),
                'Datos Finca'[AñoMes] IN FILTERS ( 'Datos Finca'[AñoMes] )
            ),
            'Datos Finca'[Sede]
        ),
        "M12Value", [Unid x M2 12M]
    )
VAR ti =
    ADDCOLUMNS ( t, "index", RANKX ( t, [M12Value],, ASC ) )
RETURN
    MAXX (
        FILTER ( ti, [index] = ROUND ( COUNTROWS ( t ) * 0.75, 0 ) ),
        [M12Value]
    )

We suggest you to delete the the link you have shared if it contain any confidential information or it come from real data and it seems that you set edit permission within your shared link which is dangerous in a public forum.

BTW, pbix as attached.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.