percentile.inc in a matrix based on a column values

[Anonymous]

Hey there!

File here

I'm trying to create a quartile matrix by using the PERCENTILE.INC() function. When I simply use one of the Q vars listed bellow the table properly calculates the percentile. However, I'd like to create a condition that states the quarter based on a value in a table (within the 'Produtividade' column"). As shown in the image, the formula bears an error. How can I fix that?

Quartil = 
var Q1 = calculate ( PERCENTILE.inc ('QRTL'[produtividade] , 0)   , ALLselected('QRTL'[week start]) , ALLselected('QRTL'[assignee]))
var Q2 = calculate ( PERCENTILE.inc ('QRTL'[produtividade] , 0.25), ALLselected('QRTL'[week start]) , ALLselected('QRTL'[assignee]))
var Q3 = calculate ( PERCENTILE.inc ('QRTL'[produtividade] , 0.5) , ALLselected('QRTL'[week start]) , ALLselected('QRTL'[assignee]))
var Q4 = calculate ( PERCENTILE.inc ('QRTL'[produtividade] , 0.75), ALLselected('QRTL'[week start]) , ALLselected('QRTL'[assignee]))

return SWITCH(TRUE(),
                Qrtl[Produtividade] > Q1 , "Q1",
                Qrtl[Produtividade] > Q2 , "Q2",
                Qrtl[Produtividade] > Q3 , "Q3",
                Qrtl[Produtividade] > Q4 , "Q4",
                blank () )

Here is what the desired table looks like, but instead of the “Quartil” values, I'd like to calculate what Q is the person in a given week.

Thanks in advance.

[Anonymous · Accepted solution]

I myself found a solution:

Quartil = 
var Q25 = calculate ( PERCENTILEX.INC('QRTL semanal', 'QRTL semanal'[produtividade] * 1 , 0.25)  , ALLselected('QRTL semanal'[week start]) , ALLselected('QRTL semanal'[assignee]))
var Q50 = calculate ( PERCENTILEX.INC('QRTL', 'QRTL'[produtividade] * 1 , 0.5)   , ALLselected('QRTL'[week start]) , ALLselected('QRTL'[assignee]))
var Q75 = calculate ( PERCENTILEX.INC('QRTL', 'QRTL'[produtividade] * 1 , 0.75) , ALLselected('QRTL'[week start]) , ALLselected('QRTL'[assignee]))
var geral = calculate ( PERCENTILEX.INC('QRTL', 'QRTL'[produtividade] * 1 , 0.75))
var calculo = switch (
            true () ,
            Q25 > geral , "Q1" ,
            Q50 > geral , "Q2" ,
            Q75 > geral , "Q3" ,
            Q75 < geral , "Q4" ,
            blank () )
return calculo