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 ...
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

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

  • McCow's avatar
    McCow
    Resolver III
    8 years ago

    Hi micky123,

     

    to build a column like your example, you can create DAX table (for ex. PersentilleT):

    PersentilleT = UNION(GENERATESERIES(0;0,05;0,01);GENERATESERIES(0,1;0,9;0,05);GENERATESERIES(0,91;1;0,01))

    Now you have one-column table with "Value" column.


    And as second calculated column you can use this formula:

    Column = PERCENTILEX.INC(Table1;Table1[Cost];[Value])

     It's all as you need?

    P.S. for dynamic k-value you need to build a measure. It' is a little complicated approach.

     

    Best Regs

    • micky123's avatar
      micky123
      Frequent Visitor
      8 years ago

      McCow Thanks this was helpful and I think I am getting close to what I am trying to do.

      I am unable to get this work 

      Column = PERCENTILEX.INC(Table1;Table1[Cost];[Value])

      I have created a sample PowerBI file using your suggestion. URL: https://1drv.ms/u/s!Aghc93erq8rBcLygGX6qE2p7zXY

      Please let me know if this simplify what I am after.

      Thanks

      • McCow's avatar
        McCow
        Resolver III
        8 years ago

        Hi micky123

        good job, you was a pretty near.

        i corrected you PBIX example and send back, LOOK HERE:

         

        Two remarks:

        1) The percentile calculation must be a column, not measure (see above)

        2) The source Data type must be "Decimal" not Whole (see bellow):

         

         

         

         

         

         

         

         

         

         

        And i added extra columns for better understanding you troubles. It can be leaved to better understanding, what i did. Enjoy!

        If you have a questions ask back.

        Best regs