topic Re: Create a measure using count, if and sum in the same expression in App Development

Create a measure using count, if and sum in the same expression

mendes094 — Tue, 04 Feb 2025 13:46:52 GMT

Hello,

I need to create a master item measure that calculates how many values of the master item measure Supply Priority have the value 'Zero'.

The master item measure of Supply Priority is:

if(SUM(ABASTECIMENTO_COTA) <= 0, 'Sem Cota',

if((SUM(ABASTECIMENTO_COTA) + Sum(ESTOQUE_IDEAL_COTA)) < (SUM(QT_FAT) + [POSICIONADO] + [TRANSITO_AB] + [PROGRAMADO_AB]), 'Extra Meta',

if(SUM(ABASTECIMENTO_COTA) > 0 AND [POSICIONADO] = 0 AND [TRANSITO_AB] = 0 AND [PROGRAMADO_AB] = 0, 'Zero',

IF(SUM(ABASTECIMENTO_COTA) > 0 AND [POSICIONADO] = 0 AND [TRANSITO_AB] + [PROGRAMADO_AB] > 0, 'Rompido',

if((Sum(ESTOQUE_IDEAL_COTA) + CARTEIRA_CD) - ([POSICIONADO] + [TRANSITO_AB] + [PROGRAMADO_AB]) < 0, 'Acima Ideal', 'Abaixo Ideal')))))

Note: POCISIONADO, TRANSITO_AB and PROGRAMADO_AB are also master item measure.

I try something like this, but doesn't work:
Count(Aggr(
If(Sum(ABASTECIMENTO_COTA) > 0 AND POSICIONADO = 0 AND
TRANSITO_AB = 0 AND PROGRAMADO_AB = 0, DFU_COTA), DFU_COTA))

and

Count(If(SUM(ABASTECIMENTO_COTA) > 0 AND [POSICIONADO] = 0 AND [TRANSITO_AB] = 0 AND [PROGRAMADO_AB] = 0, DFU_COTA))

Anybody can help me?

tks!

Re: Create a measure using count, if and sum in the same expression

Chanty4u — Tue, 04 Feb 2025 14:49:11 GMT

Try this

Count(

Aggr(

If(

SUM(ABASTECIMENTO_COTA) > 0

AND [POSICIONADO] = 0

AND [TRANSITO_AB] = 0

AND [PROGRAMADO_AB] = 0,

DFU_COTA

)

Re: Create a measure using count, if and sum in the same expression

diegozecchini — Tue, 04 Feb 2025 15:57:49 GMT

Hi! Alternatively

If DFU_COTA is already a distinct dimension in your dataset, you may not need Aggr():

Count(

DISTINCT If(

SUM(ABASTECIMENTO_COTA) > 0 AND
[POSICIONADO] = 0 AND
[TRANSITO_AB] = 0 AND
[PROGRAMADO_AB] = 0, DFU_COTA

)