Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Icon for Administrator rankAdministrator
3 years ago

The percentile function does not work with dynamic filter

Good day community!

I am having a problem using the PERCENTILE.INC. function.

The objective is to generate a new column that indicates in which quartile each seller is based on the sales made

I am looking for this quartile value to be calculated for each month, that is, once the filter is made, the corresponding quartile is assigned based only on the sales of that month

The file I am uploading contains sales per user for October, November and December, I have previously calculated a column Q_ventas, which assigns the quartile value to each sale calculated on the total sales of each month individually.

I have managed to validate that the function I am applying is correct because when I load the months separately the quartile values coincide. The problem is when I load the full summary and filter per month. The quartiles stop matching and I can't match them as they really should.

NOTE: I understand that at a general level the quartiles would not coincide since the calculation of them should be made on the total matrix and not month by month as I have done simply to validate that it works.

The calculation of the quartile to which it corresponds is done through the switch function, from a new column calculated as follows:

Q_Ventas2 =
SWITCH(TRUE(),
Sheet1[Sales] <= PERCENTILE. INC(Sheet1[Sales], 0.25), "Q1",
Sheet1[Sales] <= PERCENTILE. INC(Sheet1[Sales], 0.50), "Q2",
Sheet1[Sales] <= PERCENTILE. INC(Sheet1[Sales], 0.75), "Q3",
"Q4")

I hope you can help me, thanks from now!

No RepliesBe the first to reply