SUMX with more than one filters and IF

[Anonymous]

Olá, tenho a Matriz abaixo com as seguintes métricas

TesteCad =

var DataInicial = MIN('Date'[Date])

var DataFinal = MAX('Date'[Date])

var _Lote = VALUES(LotDailyCadence[LotNumber])

Return

DIVIDE(

SUMX(FILTER(All(LotDailyCadence),LotDailyCadence[Date]>= DataInicial && LotDailyCadence[Date]<= DataFinal && LotDailyCadence[LotNumber] IN _Lote),LotDailyCadence[TotalProgrammableCadence]),1000)

TesteTotal =

var DataInicial = MIN('Date'[Date])

var DataFinal = MAX('Date'[Date])

var _Lote = VALUES(LotDailyCadence[LotNumber])

Return

DIVIDE(SUMX(FILTER(all(LotDailyCadence),LotDailyCadence[Date]= DataFinal && LotDailyCadence[LotNumber] IN _Lote),LotDailyCadence[Lots.TotalQuantity]),1000)

TesteCad4 =

var DataInicial = MIN('Date'[Date])

var DataFinal = MAX('Date'[Date])

var _Lote = values(LotDailyCadence[LotNumber])

var _Check =

IF(DIVIDE(SUMX(FILTER(LotDailyCadence,LotDailyCadence[Date]>= DataInicial && LotDailyCadence[Date]<= DataFinal && LotDailyCadence[LotNumber] IN _Lote),LotDailyCadence[TotalProgrammableCadence]),1000)>

DIVIDE(SUMX(FILTER(LotDailyCadence,LotDailyCadence[Date]= DataFinal && LotDailyCadence[LotNumber] IN _Lote),LotDailyCadence[Lots.TotalQuantity]),1000),

DIVIDE(SUMX(FILTER(LotDailyCadence,LotDailyCadence[Date]= DataFinal && LotDailyCadence[LotNumber] IN _Lote),LotDailyCadence[Lots.TotalQuantity]),1000),

DIVIDE(SUMX(FILTER(LotDailyCadence,LotDailyCadence[Date]>= DataInicial && LotDailyCadence[Date]<= DataFinal && LotDailyCadence[LotNumber] IN _Lote),LotDailyCadence[TotalProgrammableCadence]),1000))

return

_Check

Quando coloco a métrica para verificar if(TesteCad>TesteTotal, TesteTotal, TesteCad) a somatória final não bate.

Alguem poderia me ajudar??

[Anonymous]

this worked, thanks a lot for the help

[v-xiaotang · Accepted solution]

Hi @Anonymous 

Thanks for reaching out to us.

The first 2 measures are working fine, right? So for TesteCad4, you can try the measure below. (As there is no sample data to test, not sure if it works, just try it.)

create the measure, and put it into table visual,

testTesteCad4 =
IF (
    HASONEFILTER ( LotDailyCadence[LotNumber] ),
    [TesteCad4],
    SUMX (
        FILTER (
            LotDailyCadence,
            LotDailyCadence[Date] >= DataInicial
                && LotDailyCadence[Date] <= DataFinal
                && LotDailyCadence[LotNumber] IN _Lote
        ),
        [TesteCad4]
    )
)

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.