HOW TO SUM A VARIABLE THAT COMES FROM A MEASURE

[valentinaliac]

Hello guys!

I have to resume values from 3 different lines of production, which are "Lee Pack", "Rad Pack" and "Manual". To calculate the variable "Velocidad teorica" I use this conditional: 

Velocidad teorica = IF(OR([promedio]=1, [promedio]=2), (SUM(Estandares[Rendimiento maquina])/450), IF([promedio]=3,(SUM(Estandares[Rendimiento manual])/450), "0"))

where 1 means "Lee Pack", 2 means "Rad Pack" and 3 means "Manual"

Due to it is a conditional, in the table's total the value shown is the condition if false from the second conditional.

Other variable is "Tiempo Teórico" where 

tiempo teorico = sum(Hora[No. Unidades])/[Velocidad teorica]

so if "Velocidad teórica" is 0, then it would show infinite.

Lastly, the variable "% CUMPLIMIENTO" is calculated:
% CUMPLIMIENTO = if(([tiempo teorico]/[tiempo real])>100, "0", ([tiempo teorico]/[tiempo real]))

My problem is that I can´t visualize the sum of "tiempo teórico", and i Think its because the conditional, same as "velocidad teórica" and "% cumplimiento". Do you know a way to do it?

The data table is available in the link 

https://docs.google.com/spreadsheets/d/1QcCAdk9oMWi__oWiYaWkTop5OXn5feCg/edit?usp=sharing&ouid=10393...

https://drive.google.com/drive/folders/1BpRDGXwfnV7QKtacQkmnR9kM7pIuxH06?usp=sharing

[v-jianboli-msft · Accepted solution]

Hi @valentinaliac ,

After my test, the calculation of "New  % Cumplimiento" Works fine, this issue is caused by the aggregation of No.Unidades.

Please check and make sure it is using "Don't Summarize". In your sample, it is using Sum, so the calculation looks wrong.

Best Regards,

Jianbo Li

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

[v-jianboli-msft]

Hi @valentinaliac ,

Based on your sample, I have modified some of your measure, please try:

New velocidad real = SUMX(FILTER('Hora',[Total de horas]<>0),[No. Unidades]/([Total de horas]*60))

New Velocidad teorica =
VAR _a =
    ADDCOLUMNS (
        SUMMARIZE (
            'Hora',
            'Hora'[Hora inicial],
            'Hora'[No. Unidades],
            'Hora'[Empaque]
        ),
        "VT",
            IF (
                OR ( [promedio] = 1, [promedio] = 2 ),
                ( SUM ( Estandares[Rendimiento maquina] ) / 450 ),
                IF ( [promedio] = 3, ( SUM ( Estandares[Rendimiento manual] ) / 450 ), "0" )
            ),
        "PREVISTAS", [PREVISTAS],
        "ProductionReal", [Produccion real]
    )
VAR _b =
    DISTINCT (
        FILTER ( _a, [PREVISTAS] <> BLANK () && [ProductionReal] <> BLANK () )
    )
RETURN
    SUMX ( _b, [VT] )

New % CUMPLIMIENTO =
VAR _a =
    SUMMARIZE (
        'Hora',
        'Hora'[Hora inicial],
        'Hora'[No. Unidades],
        'Hora'[Empaque]
    )
VAR _b =
    ADDCOLUMNS (
        _a,
        "%",
            IF (
                ( [tiempo teorico] / [total minuto] ) > 100,
                "0",
                ( [tiempo teorico] / [total minuto] )
            )
    )
RETURN
    SUMX ( FILTER ( _b, [total minuto] <> 0 && [tiempo teorico] <> BLANK () ), [%] )

Final output:

Best Regards,

Jianbo Li

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

[valentinaliac]

Hi @v-jianboli-msft 

Thank you again for your help!

I was taking a look to the formulas and found something strange. 

I have this case where the % Cumplimiento is not being calculated properly. Maybe you can find why?

I uploaded the document I'm working on in the same link. 

https://drive.google.com/drive/folders/1BpRDGXwfnV7QKtacQkmnR9kM7pIuxH06

Than you!

[v-jianboli-msft]

Hi @valentinaliac ,

What is the relationship between your tables and what is the other measures(such as [tiempo real])?

I can't reproduce your problem just from your source data, need more details about your PBIX file.

Can you please share more details to help us clarify your scenario?

Please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.

Refer to:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

Best Regards,

Jianbo Li

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

[valentinaliac]

Hi, @v-jianboli-msft 

Thank you for the interest!

I´ve posted the pbix file in the link. Let me know if you have more questions!

https://drive.google.com/drive/folders/1BpRDGXwfnV7QKtacQkmnR9kM7pIuxH06?usp=sharing