Fractile with AGGR on two dimensions but including only one dimension on table

[Juhap]

Hi all,

I have data containing dates, days of week, and customerIDs, and I am trying to calculate top quartile for the number of distinct customers per day of the week. For example, as 1st of April is Thursday, I would like to get what is the top quartile for number of distinct customers within all Thursdays in my data. I could easily get this to work by adding Day of the week as another dimension, but then I cannot use this as a bar graph.

I managed to calculate the average number of distinct customers per weekday with following:

=Sum(aggr(count(distinct total <DayOfWeek> CustomerID), DayOfWeek, Date))
/
Sum(aggr(count(total <DayOfWeek>  Date), DayOfWeek, Date))

Here's what my table is currently looking like:

Date	Number of distinct customers per date	Average number of distinct customers per day of the week	Top quartile for number of distinct customers per day of the week
1st Apr 2021	2942	2648	x
2nd Apr 2021	2897	2622	x
3rd Apr 2021	2776	2467	x
4th Apr 2021	2676	2418	x
5th Apr 2021	1755	1633	x

Thanks in advance!