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

Try these two:

Quartile 2_City =
VAR _A =
    SUMMARIZE( Sheet1, Sheet1[Date], Sheet1[City], "M", [Margin] )
RETURN
    PERCENTILEX.EXC( _A, [M], .5 )

 

Quartile_Country =
VAR _A =
    SUMMARIZE( Sheet1, Sheet1[Date], Sheet1[Country], "M", [Margin] )
RETURN
    PERCENTILEX.EXC( _A, [M], .5 )

 

Output:

 

 

 

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

Both of them are returning same values 😞

 

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

    Change Country to this:

    Quartile_Country = 
VAR _A =
    calculatetable(SUMMARIZE( Sheet1, Sheet1[Date], Sheet1[Country], "M",[Margin]),removefilters(Sheet1[City]))
RETURN
    PERCENTILEX.EXC( _A, [M], .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

    Sorry, that doesn't seem to change either