Percentiles using DAX using COUNT and Variables

[Anonymous]

Hello,

so i want to calulate the 25 Percentile and the 75 Percentile for the total number of customers who came to my store based on daily data. So i have a customer ID, i have more than 200 store locations, and i have daily data for the customer IDs who came to my store.

What i want here is to do a distinctcount for the customers who came to my store DAILY, and then see the 25 Percentile for the number of customers who came to my store last year based on the daily data, and then to rank them based on the store location.

For an example,

In  Boston Store, on 01.02.2020 500 customer came (200 Unique Customer ID)

In  Boston Store, on 01.03.2020 200 customer came (140 Unique Customer ID)

In  Boston Store, on 01.04.2020 900 customer came (300 Unique Customer ID)

In  Boston Store, on 01.05.2020 1000 customer came (110 Unique Customer ID)

So for the Boston store, for those 4 days, the 25 percentile would be 110.

I of course have more complex data and more than 200 store locations. All i found online was Percentile for the sum and not for the count / distinctcount.

This link was a bit helpful, but not really what i look for:

https://xcelanz.com/dynamic-percentiles-using-dax/

Thanks 🙂

[Anonymous]

@Anonymous 

This sounds doable, please provide a sample data of you model.

Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

hi @Anonymous 

Thank you for your reply ! Here is a sample data that i work with:

CustomerID	Date	Sales	Location
1	01/01/2020	2	Boston
2	01/01/2020	4	Chicago
3	02/01/2020	5	New York
4	02/01/2020	6	Boston
5	03/01/2020	8	Chicago
6	04/01/2020	10	New York
7	05/01/2020	12	Boston
8	01/01/2020	14	Chicago
9	01/01/2020	16	New York
10	02/01/2020	18	Boston
11	02/01/2020	20	Chicago
12	03/01/2020	22	New York
13	04/01/2020	24	Boston
14	05/01/2020	26	Chicago
15	01/01/2020	28	New York
16	01/01/2020	30	Boston
17	02/01/2020	32	Chicago
18	02/01/2020	34	New York
19	03/01/2020	36	Boston
20	04/01/2020	38	Chicago
1	05/01/2020	40	New York
2	01/01/2020	42	Los Angelos
3	01/01/2020	44	Los Angelos
4	02/01/2020	46	Los Angelos
5	02/01/2020	48	Los Angelos
6	03/01/2020	50	Los Angelos
7	04/01/2020	52	Los Angelos
8	05/01/2020	54	Los Angelos
9	11/01/2020	56	New York
10	12/01/2020	58	New York
11	13/01/2020	60	New York
12	14/01/2020	62	New York
13	02/01/2020	64	New York
14	03/01/2020	66	New York
15	04/01/2020	68	New York
16	05/01/2020	70	New York
17	01/01/2020	72	Boston
18	01/01/2020	74	Boston
19	02/01/2020	76	Boston
20	02/01/2020	78	Boston
15	03/01/2020	80	Boston
16	24/01/2020	82	New York
17	25/01/2020	84	Boston
18	26/01/2020	86	Chicago
19	27/01/2020	88	New York
20	28/01/2020	90	Boston
15	06/01/2020	92	Chicago
16	02/01/2020	94	New York
17	02/01/2020	96	Boston
18	03/01/2020	98	Boston
19	24/01/2020	100	New York
20	25/01/2020	102	Boston
15	06/01/2020	104	Chicago
16	02/01/2020	106	Boston
17	02/01/2020	108	Boston
18	03/01/2020	110	New York
19	24/01/2020	112	Boston
20	25/01/2020	114	Chicago
18	06/01/2020	116	Boston
18	02/01/2020	118	New York
18	02/01/2020	120	Boston
18	03/01/2020	122	Chicago
18	24/01/2020	124	Boston
18	25/01/2020	126	Chicago
18	06/01/2020	128	Boston
16	02/01/2020	130	Chicago
17	02/01/2020	132	Boston
18	03/01/2020	134	Chicago
19	24/01/2020	136	Chicago
20	25/01/2020	138	Chicago
15	04/01/2020	140	Chicago
16	05/01/2020	142	Chicago
16	11/01/2020	144	Chicago
17	12/01/2020	146	Chicago
18	13/01/2020	148	Chicago
19	14/01/2020	150	New York
20	02/01/2020	152	Boston
15	06/02/2020	154	Chicago
16	05/01/2020	156	New York
16	01/01/2020	158	Boston
17	01/01/2020	160	Boston
18	02/01/2020	162	New York
19	02/01/2020	164	Boston
20	03/01/2020	166	New York
15	24/01/2020	168	Boston
20	25/01/2020	170	Chicago
20	26/01/2020	172	New York
20	27/01/2020	174	Boston
17	05/01/2020	176	Boston
17	01/01/2020	178	New York
17	01/01/2020	180	New York
17	02/01/2020	182	New York
17	02/01/2020	184	New York
17	03/01/2020	186	New York
20	01/01/2020	188	New York
15	02/01/2020	190	New York
20	02/01/2020	192	New York
20	03/01/2020	194	New York
20	04/01/2020	196	New York
17	05/01/2020	198	New York
17	01/01/2020	200	Chicago
17	01/01/2020	202	Chicago
17	02/01/2020	204	Chicago
17	02/01/2020	206	Chicago
20	03/01/2020	208	Chicago
15	04/01/2020	210	Chicago
20	01/01/2020	212	Chicago
20	02/01/2020	214	New York
20	02/01/2020	216	New York
17	03/01/2020	218	New York
17	04/01/2020	220	New York
17	05/01/2020	222	New York
17	01/01/2020	224	New York
20	01/01/2020	226	New York
15	02/01/2020	228	New York
20	02/01/2020	230	New York
20	03/01/2020	232	Chicago
20	04/01/2020	234	Chicago
17	01/01/2020	236	Chicago
17	02/01/2020	238	Chicago
17	02/01/2020	240	Chicago
17	03/01/2020	242	Chicago
18	04/01/2020	244	Chicago
19	05/01/2020	246	Chicago

[Anonymous]

Hi @V-pazhen-msft

CustomerID	Date	Sales	Equipment
1	01/01/2020	2	Cars
2	01/01/2020	4	Bicycle
3	02/01/2020	5	Planes
4	02/01/2020	6	Cars
5	03/01/2020	8	Bicycle
6	04/01/2020	10	Planes
7	05/01/2020	12	Cars
8	01/01/2020	14	Bicycle
9	01/01/2020	16	Planes
10	02/01/2020	18	Cars
11	02/01/2020	20	Bicycle
12	03/01/2020	22	Planes
13	04/01/2020	24	Cars
14	05/01/2020	26	Cars
15	01/01/2020	28	Bicycle
16	01/01/2020	30	Planes
17	02/01/2020	32	Cars
18	02/01/2020	34	Bicycle
19	03/01/2020	36	Planes
20	04/01/2020	38	Cars
1	05/01/2020	40	Bicycle
2	01/01/2020	42	Planes
3	01/01/2020	44	Cars
4	02/01/2020	46	Bicycle
5	02/01/2020	48	Planes
6	03/01/2020	50	Cars
7	04/01/2020	52	Cars
8	05/01/2020	54	Bicycle
9	11/01/2020	56	Planes
10	12/01/2020	58	Cars
11	13/01/2020	60	Bicycle
12	14/01/2020	62	Planes
13	02/01/2020	64	Cars
14	03/01/2020	66	Bicycle
15	04/01/2020	68	Planes
16	05/01/2020	70	Cars
17	01/01/2020	72	Bicycle
18	01/01/2020	74	Planes
19	02/01/2020	76	Cars
20	02/01/2020	78	Cars
15	03/01/2020	80	Bicycle
16	24/01/2020	82	Planes
17	25/01/2020	84	Cars
18	26/01/2020	86	Bicycle
19	27/01/2020	88	Planes
20	28/01/2020	90	Cars
15	06/01/2020	92	Bicycle
16	02/01/2020	94	Planes
17	02/01/2020	96	Cars
18	03/01/2020	98	Bicycle
19	24/01/2020	100	Planes
20	25/01/2020	102	Cars
15	06/01/2020	104	Cars
16	02/01/2020	106	Bicycle
17	02/01/2020	108	Planes
18	03/01/2020	110	Cars
19	24/01/2020	112	Bicycle
20	25/01/2020	114	Planes
18	06/01/2020	116	Cars
18	02/01/2020	118	Bicycle
18	02/01/2020	120	Planes
18	03/01/2020	122	Cars
18	24/01/2020	124	Bicycle
18	25/01/2020	126	Planes
18	06/01/2020	128	Cars
16	02/01/2020	130	Cars
17	02/01/2020	132	Bicycle
18	03/01/2020	134	Planes
19	24/01/2020	136	Cars
20	25/01/2020	138	Bicycle
15	04/01/2020	140	Planes
16	05/01/2020	142	Cars
16	11/01/2020	144	Bicycle
17	12/01/2020	146	Planes
18	13/01/2020	148	Cars
19	14/01/2020	150	Bicycle
20	02/01/2020	152	Planes
15	06/02/2020	154	Cars
16	05/01/2020	156	Cars
16	01/01/2020	158	Bicycle
17	01/01/2020	160	Planes
18	02/01/2020	162	Cars
19	02/01/2020	164	Bicycle
20	03/01/2020	166	Planes
15	24/01/2020	168	Cars
20	25/01/2020	170	Bicycle
20	26/01/2020	172	Planes
20	27/01/2020	174	Cars
17	05/01/2020	176	Bicycle
17	01/01/2020	178	Planes
17	01/01/2020	180	Cars
17	02/01/2020	182	Cars
17	02/01/2020	184	Bicycle
17	03/01/2020	186	Planes
20	01/01/2020	188	Cars
15	02/01/2020	190	Bicycle
20	02/01/2020	192	Planes
20	03/01/2020	194	Cars
20	04/01/2020	196	Bicycle
17	05/01/2020	198	Planes
17	01/01/2020	200	Cars
17	01/01/2020	202	Bicycle
17	02/01/2020	204	Planes
17	02/01/2020	206	Cars
20	03/01/2020	208	Cars
15	04/01/2020	210	Bicycle
20	01/01/2020	212	Planes
20	02/01/2020	214	Cars
20	02/01/2020	216	Bicycle
17	03/01/2020	218	Planes
17	04/01/2020	220	Cars
17	05/01/2020	222	Bicycle
17	01/01/2020	224	Planes
20	01/01/2020	226	Cars
15	02/01/2020	228	Bicycle
20	02/01/2020	230	Planes
20	03/01/2020	232	Cars
20	04/01/2020	234	Cars
17	01/01/2020	236	Bicycle
17	02/01/2020	238	Planes
17	02/01/2020	240	Cars
17	03/01/2020	242	Bicycle
18	04/01/2020	244	Planes
19	05/01/2020	246	Cars