CUSTOM PERCENTILE FAIL

[omiranda]

Hi everyone.

I have a table that contains one record per year, I need to calculate the percentile for each of them, referencing another table that has the records to apply but when I do it it does not give the exact value, when I calculate it in excel or another medium always comes out the same minus here. The formula is the following.

GCuartilMediana =

var YearEval = MAX(Calendario[YearEval])
var MyTabla = CALCULATETABLE (
Evaluacion
; FILTER ( ALLEXCEPT ( Evaluacion
; Evaluacion[Empresa]
; Evaluacion[Area]
; Evaluacion[UO]
; Evaluacion[NivelEquivalente]
)
; Evaluacion[YearEval] = YearEval
&& ISBLANK(Evaluacion[GLO_Media]) = FALSE()
)
)

RETURN
PERCENTILEX.EXC(MyTabla; [GLO_Media]; [CuartilMediana]) + 0

Can you help me?. Thanks a lot.

[CuartilMediana] = 0.5

Final Result = 109.61

True Result = 109.47

[v-lili6-msft]

hi  @omiranda 

Sample data and expected output would help tremendously.
Please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Regards,

Lin

[omiranda]

Hi.

The problem is with DirectQuery with SQL. The import mode is good. 

The problem is that it is a table filtered only by the last three years since the last one selected.

Import Mode

DirectQuery

Thanks a lot. Regards.

[omiranda]

Year	Origin	Media
2020	MUTUACTIV S.G	114,67
2020	INVERSIONES	108,00
2020	MUTUACTIV S.G	114,17
2020	INVERSIONES	105,33
2020	INVERSIONES	104,17
2020	MUTUACTIV S.G	111,83
2020	INVERSIONES	107,50
2020	INVERSIONES	105,17
2020	MUTUACTIV S.G	107,61
2020	INVERSIONES	113,67
2020	INVERSIONES	104,33
2020	MUTUACTIV S.G	114,67
2020	INVERSIONES	107,83
2020	INVERSIONES	109,33
2020	INVERSIONES	112,83
2020	INVERSIONES	110,33
2020	MUTUACTIV S.G	110,67
2020	MUTUACTIV S.G	114,67
2020	INVERSIONES	106,50
2020	INVERSIONES	112,83
2020	INVERSIONES	101,67
2020	MUTUACTIV S.G	110,50
2020	INVERSIONES	105,50
2020	MUTUACTIV S.G	111,33
2020	MUTUACTIV S.G	105,83
2020	MUTUACTIV S.G	110,67
2020	MUTUACTIV S.G	109,17
2020	MUTUACTIV S.G	114,50
2020	MUTUACTIV S.G	117,86
2020	MUTUACTIV S.G	111,17
2020	INVERSIONES	110,00
2020	MUTUACTIV S.G	115,83
2020	MUTUACTIV S.G	108,53
2020	MUTUACTIV S.G	117,00
2020	INVERSIONES	109,00
2020	INVERSIONES	106,33
2020	INVERSIONES	107,14
2020	MUTUACTIV S.G	111,17
2020	MUTUACTIV S.G	114,67

It is a sample, therefore it will not give the result indicated in the question
Thanks for your help😊

[v-lili6-msft]

hi  @omiranda 

I couldn't use this measure in your above sample data, if possible, could you please share a pbix file that by import mode, that will be a great help.

Regards,

Lin