Forum Discussion

2366's avatar
2366
Icon for Helper I rankHelper I
4 years ago

DAX - Percentile Calculation at different granularity

Hello,   I have a data set which is at multiple granularity - Region, Country, City, Daily with Profit as my measure Now I want to calculate percentiles at different grain dynamically.   ...
VahidDM's avatar
VahidDM
Icon for Super User rankSuper User
4 years ago

Hi 2366 

 

Try this:

CALCULATE(PERCENTILEX.EXC(ALLEXCEPT(Sheet1,Sheet1[City],Sheet1[Month]),[Margin],.5))

 

 

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

 

 

  • 2366's avatar
    2366
    Icon for Helper I rankHelper I
    4 years ago

    Hi VahidDM ,

    Thanks but it returns blank as it doesnt seem to calculate percentiles at Monthly/City grain

     

     

    • VahidDM's avatar
      VahidDM
      Icon for Super User rankSuper User
      4 years ago

      2366 
      Try this:

      for City:
      PERCENTILEX.EXC(Sheet1,[Margin],.5))
      For Country :
      CALCULATE(PERCENTILEX.EXC(Sheet1,[Margin],.5),removefilters(Sheet1[City]))

       

      If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
      Appreciate your Kudos!!
      LinkedIn:       www.linkedin.com/in/vahid-dm/

       

       



      • 2366's avatar
        2366
        Icon for Helper I rankHelper I
        4 years ago

        VahidDM that worked partially but it appears the filter for month is also being removed.

        I need the percentiles to be reset for every month/year