How to group by columns for PERCENTILE functions?

Anonymous — Thu, 20 Aug 2020 14:43:06 GMT

I have a CALULATETABLE inside which I use a SUMMARIZE function to group employees and their respective performance metrics aggregated like:

Employee	Metric_1	Metric_2	Metric_3
A	60	73	24
B	56	35	87

The date range of this calculation by default is a rolling 45, which I define within the summarize function. After which, I use PERCENTILE functions to see which of the employees are in the top 25,50,75 brackets for each metric. For example:

Bottom 25%_Metric 1 = CALCULATE(PERCENTILE.INC(Quartile[Metric 1],0.25), ALL(Quartile))

My new requirement is, I need to include dates into this table, as we want to see how employees have performed over different date ranges, instead of a flat rolling 45. When I attempt to do this, my table looks like this:

Employee	Metric_1	Metric_2	Metric_3	Date
A	6	7	2	8/19/2020
A	5	3	2	8/20/2020

The problem is, when I feed Metric 1, 2 and 3 into PERCENTILE, it doesn't allow me to group the column data ignoring the date, but rather it calculates the day to day as the percentile. Is there a way to get around this? Or if there are other ways to do this, it would be really helpful. Thank you!

Re: How to group by columns for PERCENTILE functions?

Anonymous — Fri, 21 Aug 2020 13:24:36 GMT

Sorry, I don't get the description. Maybe others will...

Re: How to group by columns for PERCENTILE functions?

saraMissBI — Fri, 21 Aug 2020 13:29:11 GMT

Hi Anonymous ,

Have you tried adding all date in your formula?(see below)

Bottom 25%_Metric 1 = CALCULATE(PERCENTILE.INC(Quartile[Metric 1],0.25), ALL(Quartile), ALL(Date))

Thank you,

topic Re: How to group by columns for PERCENTILE functions? in DAX Commands and Tips

How to group by columns for PERCENTILE functions?

Re: How to group by columns for PERCENTILE functions?

Re: How to group by columns for PERCENTILE functions?