Forum Discussion

micky123's avatar
micky123
Frequent Visitor
8 years ago
Solved

Percentile table like Excel

Hi everyone,

 

I am trying to create a percentile table in Powerbi but having trouble doing that.

 

Below screenshot shows the Excel version:

 

 

From this we can see that my percentile jumps from increaing by 0.01 at 0.05 to 0.05 at 0.10.

 

So I created a "Enter Data" table in PowerBi and pasted this column values:

 

This allowed me to create a table in PowerBi with the percentile values I wanted to use but I encountered a problem after this step.

 

I have 2 tables,

stats table - contains Total Sale & Cost

size table - where I have size of place

These 2 tables have relationship.

 

Now I don't know how to use PERCENTILE.INC to create a table as per first screenshot.

I hope my description is clear, if not please let me know.

 

Thanks for your time.

 

Kind Regards,

 

Rahul

Need Help
Tips and Tricks
  • micky123's avatar
    micky123
    8 years ago

    McCow Thanks for explination, I have tried to follow the same steps for prod data and there seems to be issue.
    Could you point out what I am doing wrong please? Here's the updated pbi file: https://1drv.ms/u/s!Aghc93erq8rBcUhH5N_y1X_MXN8

    I have created 3 new tables exactly in the same structure:
    size_prod, stats_prod, Persentille_prod

     


    Thanks again for your time, patience. I am learning alot from you!

     

    Kind Regards

16 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    8 years ago

    Nope, sorry, not clear. What is the problem you encountered? What is the final result you are looking to achieve? Can you post sample data from your second table? How are the tables related?

    • micky123's avatar
      micky123
      Frequent Visitor
      8 years ago

      Hi Greg_Deckler,

       

      The final result I am looking to achieve is like this:

       

       

      Table 1

      DateIDSalesCost
      01/01/201740505114357
      02/01/201772878901416
      03/01/2017363704633810
      04/01/2017410067402805
      05/01/2017579049873220
      06/01/2017456321572348
      07/01/2017338438562135
      08/01/2017714005372159
      09/01/2017747172891483
      10/01/2017777479731922
      11/01/2017602398302107
      12/01/2017650546333405
      13/01/2017449393261584
      14/01/2017717896551328
      15/01/2017407045583636
      16/01/2017688046683165
      17/01/20174113799138
      18/01/201747474759516
      19/01/2017462447481534
      20/01/2017267077973943
      21/01/2017455124271924
      22/01/2017742766392995
      23/01/201775824605390
      24/01/2017473634953676
      25/01/2017724706853021
      26/01/201748808942736
      27/01/201732450661238
      28/01/201748837213921
      29/01/201746723857378
      30/01/2017565037762414
      31/01/2017505555303044

       

      Table 2

      IDSize
      40505980
      728787600
      36370420
      4100619000
      579042100
      45632610
      338432600
      71400420
      23441150
      234715800
      234784500
      234883100
      23554800
      2356812000
      23576950
      235862100
      236077300
      2366290
      23678200
      237021200
      237155500
      23751430
      237642900
      239413800
      24013520
      24224620
      2422711000
      242441800
      24318960
      24333120000
      24334370
      24456530
      244872500
      24498410

       

       

      Relationship is on ID columns.

       

      Thanks for your time.

       

      Kind Regards.

      • micky123's avatar
        micky123
        Frequent Visitor
        8 years ago

        I have done some research and found out that this is the dax I need to use to calculate percentile but not sure how to adapt it for each percentile group: = PERCENTILEX.INC(<table>, <expression>;, k)

         

        Does anyone know how I can have a dynamic value for "k"?

         

        Thanks

  • AlanPhan's avatar
    AlanPhan
    Frequent Visitor
    3 years ago

    @micky123 , @McCow ,
    I can understand how to calculate Percentile as solution. But now I want to calculate Percentile and group by [size_id] in the table stats_prod. How could it possible? 
    Any your help will be appriciated.