topic Re: DAX - Percentile Calculation at different granularity in DAX Commands and Tips

DAX - Percentile Calculation at different granularity

2366 — Tue, 14 Dec 2021 11:04:25 GMT

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

Re: DAX - Percentile Calculation at different granularity

VahidDM — Tue, 14 Dec 2021 11:26:00 GMT

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/

Re: DAX - Percentile Calculation at different granularity

2366 — Tue, 14 Dec 2021 11:37:46 GMT

Hi VahidDM ,

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

Re: DAX - Percentile Calculation at different granularity

VahidDM — Tue, 14 Dec 2021 11:42:00 GMT

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/

Re: DAX - Percentile Calculation at different granularity

2366 — Tue, 14 Dec 2021 11:46:51 GMT

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

Re: DAX - Percentile Calculation at different granularity

VahidDM — Tue, 14 Dec 2021 11:55:37 GMT

Do you have any Date table? what is the source of that Month Column?

Appreciate your Kudos!!
LinkedIn:www.linkedin.com/in/vahid-dm/

Re: DAX - Percentile Calculation at different granularity

2366 — Tue, 14 Dec 2021 11:57:41 GMT

No, I do not have an explicit Date table. I am using the inbuilt date hirearchy "Month" column in the visual

Re: DAX - Percentile Calculation at different granularity

VahidDM — Tue, 14 Dec 2021 12:07:58 GMT

So It would be better to add a Date table to your model then it will be easier to find a solution.
How to create a Date table:https://www.vahiddm.com/post/creating-calendar-table-with-3-steps

Can you share a sample of your PBIX file after removing sensetive data?

Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

Re: DAX - Percentile Calculation at different granularity

2366 — Tue, 14 Dec 2021 12:29:14 GMT

I have shared it in DM. I have applied the date table now

Re: DAX - Percentile Calculation at different granularity

VahidDM — Tue, 14 Dec 2021 12:39:06 GMT

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/

Re: DAX - Percentile Calculation at different granularity

2366 — Tue, 14 Dec 2021 12:54:36 GMT

Both of them are returning same values 😞

Re: DAX - Percentile Calculation at different granularity

VahidDM — Tue, 14 Dec 2021 13:04:17 GMT

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/

Re: DAX - Percentile Calculation at different granularity

2366 — Tue, 14 Dec 2021 13:15:12 GMT

Sorry, that doesn't seem to change either

Re: DAX - Percentile Calculation at different granularity

v-xiaotang — Fri, 17 Dec 2021 04:48:52 GMT

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.