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.   ...
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.