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

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
Icon for Resolver III rankResolver 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

  • micky123's avatar
    micky123
    Frequent Visitor
    8 years ago

    McCow Thanks alot! This is really helpful and also for explination what I was doing wrong. I am going to put this in production where I have quite a large dataset. Will let you know how it goes!
    Thanks again for your help.

    Kind Regards

    • McCow's avatar
      McCow
      Icon for Resolver III rankResolver III
      8 years ago

      micky123

      with a pleasure!

       

      • micky123's avatar
        micky123
        Frequent Visitor
        8 years ago

        McCow There are couple of issues I am facing, please can you advise?
        - Date selection doesn't affect anything, I would like to filter percentlie table based on different date ranges from stats[Date], do we need to modify the DAX?

        - Cost percentile total is wrong cost_percentileC = PERCENTILEX.INC(stats,stats[Cost],[Value]) should be exctly the same as what we have in stats[Cost]?

        - Sales percentile using this sales_percentileC = PERCENTILEX.INC(stats,stats[Sales],[Value]) gives me an error "Expressions that yield variant data-type cannot be used to define calculated columns."

        Thanks for your time, I really appricate your help.

        Kind Regards