Calculating percentile off of a measure

[ryanmahaffey]

I need to calculate a percentile off of a measure I created, but when I go to type the formula into the bar, the measure can't be selected for some reason. How should I proceed?

[Greg_Deckler]

Can you share your formula? Can you use the Show As | Percent of Grand Total?

[ryanmahaffey]

Can't use % of grand total, as it's a rate, and when I do that it shows values as over 100%.

That's what the table looks like right now, and PDH is what I need to do the percentile off of. It's a custom measure.

[Greg_Deckler]

Can you show the formula that you are trying to use? Are you certain it is a measure and not a calculated column? If it is a column and you are trying to use it in a measure, then you need to use an aggregation.

[ryanmahaffey]

The PDH measure formula:

PDH = sum('Sch''d Appts'[Productives])/sum('Call Hour Export'[Call Hours])

I'm trying to use PERCENTILE.EXC to calculate the percentile rank for each agent's PDH.

[Greg_Deckler]

Ah, that explains it. So, the issue here is that PERCENTILE.EXC requires a column. So, what you are going to want to do is create a table as part of your measure in which you want to use PERCENTILE.EXC. So, use SUMMARIZE or ADDCOLUMN with a VAR statment to create your "temp table" essentially. I would imagine that you would summarize by agent and include your measure in this table. Then, you can use PERCENTILE.EXC against this column in your temp table. If you supply me some sample data, I can probably put together a more specific formula.

You could also use Create Table and put your SUMMARIZE or ADDCOLUMN in that and then just reference that table and stuff.

[jhimelic]

when i define the temporary table with a variable and then pass that variable to the percentile function, it doesnt work. it appears that this approach only works with an iterator function (e.g. sumx, averagex, maxx)

For example, this doesnt work:

=VAR
Temp_Table = SUMMARIZE(ABSO_Table,ABSO_Table[Date],ABSO_Table[Sim_Run],"Column_4_Percentile",sum(ABSO_Table[RenewableGeneration]))

return
PERCENTILE.INC(Temp_Table[Column_4_Percentile],.05)

it says i need a base table to pass through as an argument to percentile.inc

interestingly though , if i use an iterator function, it works. 

No problem with the following:

=VAR
Temp_Table = SUMMARIZE(ABSO_Table,ABSO_Table[Date],ABSO_Table[Sim_Run],"Column_4_Percentile",sum(ABSO_Table[RenewableGeneration]))

return
sumx(Temp_Table,[Column_4_Percentile])

thoughts?

[jhimelic]

i solved it. there is in fact a percentilex.inc function. cant believed i missed it 

here is the solution

=VAR
Temp_Table = SUMMARIZE(ABSO_Table,ABSO_Table[Date],ABSO_Table[Sim_Run],"Column_4_Percentile",sumx(ABSO_Table,ABSO_Table[RenewableGeneration]))

return
PERCENTILEX.INC(Temp_Table,[Column_4_Percentile],.05)

[ryanmahaffey]

Is there a percentile function that doesn't require that additional table? I'm brand new to PowerBI, so I didn't really understand most of your explanation, even though it sounds like it's correct.

[Greg_Deckler · Accepted solution]

OK, I can walk you through it. Go to your modeling tab and click "New Table". Paste in a formula like the following to create your table:

Table = SUMMARIZE('My Table',[Associate],"PDH",[PDH])

So, what you want is for "My Table" to be the table where you have your associate names/ids. You want to group by these, so replace [Associate] with the actual name of your associate column in "My Table". Then you specify a name for your column "PDH" and you put your PDH measure as the calculation for that column.

You should end up with a table of associates with their PDH metrics. Then, you can reference the PDH column in this new table for PERCENTILE function.

[Anonymous]

Hola, tenía la misma pregunta para poner en el foro y, ví ésta solución que he implementado, pero no me da el resultado correcto.

Tengo éste código

Percentile Session =

VAR vTableID =

    SUMMARIZE( Sesiones, Sesiones[LApp], Sesiones[timestamp.1], "Sesiones", Sesiones[N Sesiones] )

VAR vResult =

    PERCENTILEX.INC (

        vTableID,

        [Sesiones],

        [Valor Percentil]

    )

RETURN

    vResult

Mi problema es que el percentil 100, no me da el valor del total que tengo en otra tarjeta como valor total del KPI que estoy midiendo en el percentil. Por ejemplo, tengo 100.000 sesiones como kpi en una tarjeta y en otra tarjeta, tengo ésta medida de percentil y cuando pongo el P100, no me da las 100.000 sesiones, me da aproximadamente sesiones el P100, cuando debe dar las 100.000 en el P100 ¿Alguna idea?

[xinxin63]

I have a similar question. But it is more complex. In my case, people can choose a time period from time slicer. For each product, the measure is the average of sale amont in that time period. My outcome needs to be the percentile of that measure for selected products. Do I need to create a summary table for all possible time periods? This will be too many of them.

[ryanmahaffey]

Wow that was super easy.

Thank you so much!