Only to show positive values in Table

[JASalinas]

Hi my friends, i have the next set analysis in my table

Presupuesto:

=((Sum({<[ACCNT_CODE]={"611110", "635110", "635120", "635140"}, [FNCT]={"DT*"}, [LEDGER]={"B"}, YearPeriod = {$(=(max(YearPeriod)))}>}[AMOUNT])))

Real:

=(if([YearPeriod] <= '2021' and [MonthNumPeriod] <= '1', (Sum({<[ACCNT_CODE]={"611110", "635110", "635140"},[LEDGER]={"A"}, YearPeriod = {$(=(max(YearPeriod)))}>}[AMOUNT])),
(Sum({<[ACCNT_CODE]={"611110", "635110", "635140", "368235"},[LEDGER]={"A"}, [FNCT]={"DT*"}, YearPeriod = {$(=(max(YearPeriod)))}>}[AMOUNT]))))

Diferencia:

=(if([YearPeriod] <= '2021' and [MonthNumPeriod] <= '1', (Sum({<[ACCNT_CODE]={"611110", "635110", "635140"},[LEDGER]={"A"}, YearPeriod = {$(=(max(YearPeriod)))}>}[AMOUNT])),
(Sum({<[ACCNT_CODE]={"611110", "635110", "635140", "368235"},[LEDGER]={"A"}, [FNCT]={"DT*"}, YearPeriod = {$(=(max(YearPeriod)))}>}[AMOUNT])))) -
(Sum({<[ACCNT_CODE]={"611110", "635110", "635140"}, [FNCT]={"DT*"}, [LEDGER]={"B"}, YearPeriod = {$(=(max(YearPeriod)))}>}[AMOUNT]))

But i just want to show only positive values, help me please...

[QFabian · Accepted solution]

 @JASalinas , you can take your entire formula, ask if it is positivo, show it again :

Diferencia:

if((if([YearPeriod] <= '2021' and [MonthNumPeriod] <= '1', (Sum({<[ACCNT_CODE]={"611110", "635110", "635140"},[LEDGER]={"A"}, YearPeriod = {$(=(max(YearPeriod)))}>}[AMOUNT])),
(Sum({<[ACCNT_CODE]={"611110", "635110", "635140", "368235"},[LEDGER]={"A"}, [FNCT]={"DT*"}, YearPeriod = {$(=(max(YearPeriod)))}>}[AMOUNT])))) -
(Sum({<[ACCNT_CODE]={"611110", "635110", "635140"}, [FNCT]={"DT*"}, [LEDGER]={"B"}, YearPeriod = {$(=(max(YearPeriod)))}>}[AMOUNT])) >0,

(if([YearPeriod] <= '2021' and [MonthNumPeriod] <= '1', (Sum({<[ACCNT_CODE]={"611110", "635110", "635140"},[LEDGER]={"A"}, YearPeriod = {$(=(max(YearPeriod)))}>}[AMOUNT])),
(Sum({<[ACCNT_CODE]={"611110", "635110", "635140", "368235"},[LEDGER]={"A"}, [FNCT]={"DT*"}, YearPeriod = {$(=(max(YearPeriod)))}>}[AMOUNT])))) -
(Sum({<[ACCNT_CODE]={"611110", "635110", "635140"}, [FNCT]={"DT*"}, [LEDGER]={"B"}, YearPeriod = {$(=(max(YearPeriod)))}>}[AMOUNT]))

)

[JASalinas]

Thank you so much my friend, now if you allow me to ask you other question, How can i to have a counter only positve values?... Thanks in advance!...

[QFabian]

Debería ser algo así :

count(aggr((if([YearPeriod] <= '2021' and [MonthNumPeriod] <= '1', (Sum({<[ACCNT_CODE]={"611110", "635110", "635140"},[LEDGER]={"A"}, YearPeriod = {$(=(max(YearPeriod)))}>}[AMOUNT])),
(Sum({<[ACCNT_CODE]={"611110", "635110", "635140", "368235"},[LEDGER]={"A"}, [FNCT]={"DT*"}, YearPeriod = {$(=(max(YearPeriod)))}>}[AMOUNT])))) -
(Sum({<[ACCNT_CODE]={"611110", "635110", "635140"}, [FNCT]={"DT*"}, [LEDGER]={"B"}, YearPeriod = {$(=(max(YearPeriod)))}>}[AMOUNT])), CampoNivelSuperior))

donde CampoNivelSuperior, será un campo que permita agrupar los valores, para luego contarlos.

[JASalinas]

ok, mira ya lo puse en un KPI y el resultado es cero, pero yo quisiera que solo contara los que estan en verde, Verde 16, rojos 2, porque la suma del counter es 18, la condicionante seria que sean mayores de 0 que cuente los que estan en verde y viceversa...

Gracias @QFabian por tu ayuda y por tu respuesta... saludos!...

[QFabian]

Claro, entonces toca poner toda la formula con el if, para que los cuente con el aggr

[JASalinas]

Mira hice esto:

count(Aggr(if((if([YearPeriod] <= '2021' and [MonthNumPeriod] <= '1', (Sum({<[ACCNT_CODE]={"611110", "635110", "635140"},[LEDGER]={"A"}, YearPeriod = {$(=(max(YearPeriod)))}>}[AMOUNT])),
(Sum({<[ACCNT_CODE]={"611110", "635110", "635140", "368235"},[LEDGER]={"A"}, [FNCT]={"DT*"}, YearPeriod = {$(=(max(YearPeriod)))}>}[AMOUNT])))) -
(Sum({<[ACCNT_CODE]={"611110", "635110", "635140"}, [FNCT]={"DT*"}, [LEDGER]={"B"}, YearPeriod = {$(=(max(YearPeriod)))}>}[AMOUNT])) >0,

(if([YearPeriod] <= '2021' and [MonthNumPeriod] <= '1', (Sum({<[ACCNT_CODE]={"611110", "635110", "635140"},[LEDGER]={"A"}, YearPeriod = {$(=(max(YearPeriod)))}>}[AMOUNT])),
(Sum({<[ACCNT_CODE]={"611110", "635110", "635140", "368235"},[LEDGER]={"A"}, [FNCT]={"DT*"}, YearPeriod = {$(=(max(YearPeriod)))}>}[AMOUNT])))) -
(Sum({<[ACCNT_CODE]={"611110", "635110", "635140"}, [FNCT]={"DT*"}, [LEDGER]={"B"}, YearPeriod = {$(=(max(YearPeriod)))}>}[AMOUNT]))), distinct FNCT))

pero me sigue dando cero...

[QFabian]

talvez con unos datos de prueba podriamos revisar en detalle

[JASalinas]

Amigo si quieres nos ponemos de acuerdo, y entras a mi compu con teamvier gracias nuevamente por tu ayuda,  +52 9993221163 estoy en México... y podemos probar... tu me dices perdón si estoy abusando de tu ayuda... y si das cursos estoy interesado también jejeje....