how to get quantile in power bi

[kevderbeste]

Hi everyone, i have a dataset and i need get the decile and quintile of a sales column, in order to put on each'order id' which decile or quintile its belong. 

example

ORDER ID    DATE             VENTA   QUINTILE
1                 13/01/2020     3000      5
2                 14/02/2020      2500     1

here my pbix https://1drv.ms/u/s!AtEkAF7ffIsqg8Rs7pFT9fzzvkq3cQ?e=iEqGEb

[harshnathani]

Hi @kevderbeste ,

You can  have a look at 

https://community.powerbi.com/t5/Desktop/Calculate-percentile-by-group/td-p/478239

https://dash-intel.com/powerbi/statistical_functions_percentile.php

https://www.youtube.com/watch?v=5AxtNdJ5wqk

Regards,

Harsh Nathani

Appreciate with a Kudos!! (Click the Thumbs Up Button)

Did I answer your question? Mark my post as a solution!

[Anonymous]

try this:

Table.AddColumn(tab, "in which decile I am", (r)=> Number.IntegerDivide( List.PositionOf(List.Sort(Table.SelectRows(tab, each r[id]=_[id])[sales]),r[sales],null,(x,y)=>x>=y),Number.IntegerDivide(Table.RowCount(Table.SelectRows(tab, each r[id]=_[id])),10))+1)

this is valid for decile, is easily adptable for any other percentile.

I didn't check all the situation at the ends of interval, may be some think needs to be tuned!

[Anonymous · Accepted solution]

here the code complete, if you want to do some test:

let
    id=List.Transform(List.Random(200,1111), each {"a","b","c","d"}{Number.RoundDown( _*4)}),
    sales=List.Transform(List.Random(200,2222), each Number.RoundDown(10+_*500,2)),
    tab=Table.FromColumns({id,sales},{"id","sales"}),
    #"Added Custom" = Table.AddColumn(tab, "QualeDecile", (r)=> Number.IntegerDivide( List.PositionOf(List.Sort(Table.SelectRows(tab, each r[id]=_[id])[sales]),r[sales],null,(x,y)=>x>=y),Number.IntegerDivide(Table.RowCount(Table.SelectRows(tab, each r[id]=_[id])),10))+1),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "pos", (r)=> List.PositionOf(List.Sort(Table.SelectRows(tab, each r[id]=_[id])[sales]),r[sales],null,(x,y)=>x>=y)),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "sizeDec", (r)=> Number.IntegerDivide(Table.RowCount(Table.SelectRows(tab, each r[id]=_[id])),10))
in
    #"Added Custom2"

I dont know the exact definition of quantile, perhaps the formula for calculating the percentile size should either round up or not at all.