Percentile

[inascargio]

Hi all,
I am struggling with the following problem: I have a sample data set where I need to calculate the n percentile (say 10% percentile) for records pertaining to a given group (column Area). The percentile of each group should then be reported in an additional column.

Thank you
giovanni

[Anonymous · Accepted solution]

Hi @inascargio ,

You can refer the following links to get it:

By DAX:

powerbi - Calculating percentiles by group in Power BI - Stack Overflow

P10 =
    PERCENTILEX.INC (
        ALLSELECTED ( 'Table'[Area] ),
        CALCULATE ( SUM ( 'Table'[Custom.Peso] ) ),
        0.1
    )

By Power Query:

New Percentiles Transformation in Power Query | Microsoft Power Query

Best Regards

[Anonymous · Accepted solution]

Hi @inascargio ,

You can refer the following links to get it:

By DAX:

powerbi - Calculating percentiles by group in Power BI - Stack Overflow

P10 =
    PERCENTILEX.INC (
        ALLSELECTED ( 'Table'[Area] ),
        CALCULATE ( SUM ( 'Table'[Custom.Peso] ) ),
        0.1
    )

By Power Query:

New Percentiles Transformation in Power Query | Microsoft Power Query

Best Regards

[James_Roberts]

I'm using Power Query in Excel and don't have the Percentile operation available. Is it an upgrade or add-on of some sort, or only in Power BI?

[inascargio · Accepted solution]

Hi @Anonymous ,

thanks for the reply.

I followed the DAX solution and slightly modifiied the approach as follows:

=CALCULATE(PERCENTILEX.INC('Table1  2';[Custom.Peso];0,1); ALLSELECTED('Table1  2'[Custom.Gestore]))

And I managed to get exactly the output I needed (see snapshot below).

Thanks

inascargio