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.

 

 

 

My Dax is as follows -

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

CALCULATE(PERCENTILEX.EXC(VALUES(Sheet1[Country]),[Margin],.5))

 

For example - 1) top 50 percentile for each city for each month

2) top 50 percentile for each country in a region for each month 

 

I have been able to get this to work for each country and city for all months but the percentiles need to be applied on a monthly basis. 

Can you help please?

 

Thank you

13 Replies

  • 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/

         

         



  • v-xiaotang's avatar
    v-xiaotang
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi 2366 

    Do you mean you want to get Percentile_City and Percentile_Country for each month?

    If yes, try to filter the table like bellow,

    CALCULATE(PERCENTILEX.EXC(VALUES(Sheet1[City]),[Margin],.5),filter(All(Sheet1),Sheet1[Month]=min(Sheet1[Month]) && Sheet1[City]=min(Sheet1[City])))
CALCULATE(PERCENTILEX.EXC(VALUES(Sheet1[Country]),[Margin],.5),filter(All(Sheet1),Sheet1[Month]=min(Sheet1[Month]) && Sheet1[Country]=min(Sheet1[Country])))

    If this doesn't work, could you provide a sample file? Thanks

     

     

    Best Regards,

    Community Support Team _Tang

    If this post helps, please consider Accept it as the solution to help the other members find it more quickly.