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!
