Forum Discussion

valentinaliac's avatar
valentinaliac
Frequent Visitor
3 years ago

EFFICIENCY FORMULA WITH DAX

Hi guys, 

 

Can you please help me, I´m trying to do this efficiency formula 

 

"% EFICIENCIA =

VAR _a =

SUMMARIZE (

'pcc',

'pcc'[Hora inicial],

'pcc'[No. Unidades en la hora],

'pcc'[Linea de empaque]

)

VAR _b =

ADDCOLUMNS (

_a,

"%",

IF (

( [tiempo teorico] / (sum(pcc[Total de horas])*60) > 100),

"0",

( [tiempo teorico] / (sum(pcc[Total de horas])*60)

)

))

RETURN

SUMX( FILTER ( _b, (sum(pcc[Total de horas])*60) <> 0 && [tiempo teorico] <> BLANK () ), [%] )"

 

But the formula sometimes shows me values over 100%, and this alters my reports. 

 

How can I include the condition that if the result is over 100%, it shows 100% and no more? 

 

Hope you guys can help me!

 

Regards, 

Valentina 

"DAX"
DAX SUMX

1 Reply

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    3 years ago

    Hi,

    I am not sure how your datamodel looks like but please try something like below.

    I assume [tiempo teorico] is a measure, not a column.

     

    "% EFICIENCIA =
VAR _a =
    SUMMARIZE (
        'pcc',
        'pcc'[Hora inicial],
        'pcc'[No. Unidades en la hora],
        'pcc'[Linea de empaque]
    )
VAR _b =
    ADDCOLUMNS (
        _a,
        "%",
            IF (
                [tiempo teorico]
                    / ( CALCULATE ( SUM ( pcc[Total de horas] ) ) * 60 ) > 100,
                "0",
                [tiempo teorico]
                    / ( CALCULATE ( SUM ( pcc[Total de horas] ) ) * 60 )
            )
    )
RETURN
    SUMX (
        FILTER (
            _b,
            CALCULATE ( SUM ( pcc[Total de horas] ) * 60 ) <> 0
                && [tiempo teorico] <> BLANK ()
        ),
        [%]
    )