Subset of Column using Percentile

[isamchakur]

Hello guys

I am trying to make a subset of a particular column of values using a percentile (like extracting the 80% highest values), but the percentile measure I've created doesn't work in the comparison row to row. To be more clear about the procedure I tought:

-measure Percentile1 = PERCENTILE.INC(Sheet1[Value_Cost],0.2) 

when I display this measure in a card or in a table along with other values from Sheet 1, the calculation is ok

- calculated column DataSet_Filtered1 = if(Sheet1[Value_Cost]>[Percentile1],Sheet1[Value_Cost],null)

I wanted to create a column only with the 80% highest values from the first dataset. The result of the calculation doesn't seem correct.

When I try to display only the Percentile1 in a calculated column, I've got the error (Expressions who generate variable types of data cannot be used to define calculated columns), so I couldn't see how the Percentile calculation looks like within the calculated column.

Any toughts on how to make it work?

Thanks a lot

[Anonymous]

Here's the calculated column:

DataSet_Filtered1 =
var __percentileAcrossFullSet =
	PERCENTILE.INC(
		Sheet1[Value_Cost],
		0.2
	)
return
	if(
		Sheet1[Value_Cost] > __percentileAcrossFullSet,
		Sheet1[Value_Cost]
	)

Your formula does not work because... you have to learn what context transition is, what row context is and how CALCULATE interacts with the latter.

Best
D