Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

how to get quantile in power bi

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

Interesting Links
Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    6 years ago

    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.

     

3 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        Anonymous
        Not applicable
        6 years ago

        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.