PERCENTILE with filter / conditions

[DataMark]

Hi,

Have a question about using percentiles (PERCENTILE.EXC or PERCENTILE.INC) with a filter / conditions. Suppose I have two tables, one with all sales for all items and another with sales for new produts. What I am trying to find is the 1st quartile / 25% of all sales based on the category the new product is classified in as a benchmark for success / failure. 

In the example below, I would want to return the 1st quartile for DOG. In Excel, the formula would be something like:

=PERCENTILE.INC(IF([Category]="DOG",[Sales]),0.25). I tried using FILTER in DAX, but can't seem to get the result. 

Table 1: 

Category	Sales
DOG	19
CAT	17
DOG	16
DOG	9
CAT	12
DOG	14
CAT	1
DOG	4
CAT	1
DOG	20
CAT	11

Table 2: 

Product	Category	Sales	1st Quartile
Delicious Dog Food	DOG	15	6.5

Thanks!

[v-zhangti]

Hi, @DataMark 

Column:

DOG Sales = IF([Category]="DOG",[Sales])

Measure:

Measure = PERCENTILE.INC([DOG Sales],0.25)

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DataMark]

Thanks for the reply.

I need to filter the formula for 2 reasons: 1) a measure for success needs to be calculated. This will look like =IF(Sales > Category Percentile, SUCCESS, FAILURE) and 2) Need to do the same exercise but for other product attributes, like MARKET, PRICE CLASSIFICATION (low, medium, high) and so on

[littlemojopuppy]

Hi @DataMark 

There are two functions that can probably help you.  PERCENTILE.INC and PERCENTILEX.INC.  

The formula should be this simple.

PERCENTILE.INC(
	Table[Sales],
	.25
)

Shouldn't need to filter the formula because filter context in the visualization should take care of that for you (meaning it will automatically filter for Category DOG and Product Delicious Dog Food).

Hope this helps!