Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Percentail calculation from Excel to power bi

Hello All, 

I have a Percentile calculation in excel that i need to replicate in power bi 

when i use the function in power bi i am getting a wrong value  

UNIT_IDUsageAnnual Data PointYearIn Service Fiscal yearEt Type Percentile 
35985-392120232009Wheel  5%90
359360120232007Wheel  10%112
363670120232008Wheel  15%144
495320120232015Wheel  20%167
382127120232011Wheel  25%184
516128120232016Wheel  30%195
6078010120232020Wheel  35%211
3820615120232010Wheel  40%232
3593440120232007Wheel  45%252
5456957120232017Wheel  50%271
3601560120232008Wheel  55%295
3617377120232007Wheel  60%315
5160483120232016Wheel  65%327
4129084120232012Wheel  70%347
3821393120232011Wheel  75%377
5160594120232016Wheel  80%407
3821095120232011Wheel  85%456
6267897120232021Wheel  90%532
51595100120232016Wheel  95%726

Excel Calculation for is = =PERCENTILE.EXC(B6:B218,H2) for 5% 

here is an sample data from excel 

in power bi we are getting the Et type filter from a different column 

my power bi calculation is 

5 % percentile = PERCENTILE.EXC('Usage'[Usage],0.05)
in excel we are getting for 5% is 90
in power bi i am getting for 5% is 0
How can i replicate excel function in power bi  

 

Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    3 years ago

    HI Anonymous,

    It seems like you exclude the zero and negative values from excel calculation ranges, so I'd like to suggest you to use 'PERCENTILE.EXC' function to calculate ranges with correspond conditions:

    5 % percentile =
PERCENTILEX.EXC (
    FILTER ( ALLSELECTED ( 'Usage'[Usage] ), 'Usage'[Usage] > 0 ),
    'Usage'[Usage],
    0.05
)

    PERCENTILEX.EXC – DAX Guide

    Regards,

    Xiaoxin Sheng

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    HI Anonymous,

    It seems like you exclude the zero and negative values from excel calculation ranges, so I'd like to suggest you to use 'PERCENTILE.EXC' function to calculate ranges with correspond conditions:

    5 % percentile =
PERCENTILEX.EXC (
    FILTER ( ALLSELECTED ( 'Usage'[Usage] ), 'Usage'[Usage] > 0 ),
    'Usage'[Usage],
    0.05
)

    PERCENTILEX.EXC – DAX Guide

    Regards,

    Xiaoxin Sheng