Total by PuestoTrabajo (Wrong Calculation)

[John Jairo V]

Hi, to all members of this great forum.

crossposted at : https://www.mrexcel.com/forum/power...stotrabajo-wrong-calculation.html#post5294912

The idea of the table that I present in the file is to distribute a quantity of KG of production in several Puestos de Trabajo, through participations. I calculate these participations satisfactorily, both in percentage (Measure % Participación KG Real Puesto Trabajo) and in values (Measure KG Real Puesto Trabajo por Mat = [Total KG Produccion IDO] * [% Participación KG Real Puesto Trabajo]).


At the time of adding these values disconnecting the material with ALL, the resulting value is wrong ... and I can not find the reason for this situation.


Wrong measure: KG Real Totalizado Puesto Trabajo = IF(ISNUMBER([Total KG Produccion IDO]) ; CALCULATE( [KG Real Puesto Trabajo por Mat] ; ALL(fIDO[Material])) ; BLANK() )


Could you help me calculate the correct value?


To give examples of what you should give:


For PuestoTrabajo 6702041, it should give: 27164.54 + 1601.96 = 28766.5 (it's giving 20266.77 <- Wrong. I do not even know where the value comes from)


For PuestoTrabajo 6702043, it should give: 1609.11 + 10072.59 + 11160 + 764 + 3264 + 20807.55 + 4546.68 = 52223.93 (it is giving 48863.03 <- Wrong, nor do I know where the value comes from).


and so for all the PuestosTrabajo that appear, according to the Plant filter.


If you require more information, I will gladly supply it.

I greatly appreciate the help you can give me. Blessings!

link of file: https://mega.nz/#!hgBCQKAL!6pDDp0mbN7NBaX878V1gobDflweNWzmQY-4v-yWa7OQ

crossposted at : https://www.mrexcel.com/forum/power...stotrabajo-wrong-calculation.html#post5294912

 

[Chihiro]

Still looking through your model.

But one major issue here.... is that you are ignoring Material in your calculation and aggregating calculation.

So...
% Participación KG Real Puesto Trabajo
Will = 34% for PuestoTrabajo = 6702041.

Total KG Produccion IDO = 59,483.00

So... 59,483 * 0.3400425... = 20,226.77...

You can check this by removing "Material" from your visual.

However, from the looks of things, you want to iterate over and use different % Participación KG Real Puesto Trabajo for each material.

Probably easiest way to circumvent this issue is to create calculated table that summarize based on PuestoTrabajo, or create calculated column instead of doing it in measure.

You could probably do it in measure alone... but that's going to require complex measure that's going to be very difficult to understand.

I'm out of time for now, but will revisit this one later. It is an interesting problem.

 

[John Jairo V]

Thx @Chihiro for your answer. I hope you can help me. Blessings!

 

[Chihiro]

I was stepping through your calculation logic and columns used in visual found couple of issues.

1. Is there a reason why you use PuestoTrabajo & Material from fact table and not dimension table?
2. You have Iterator function intermixed with aggregation function. I think this is causing some calculation error.

I'd recommend creating several different grain data using M. As this sort of transformation is best done at query stage.

As calculated table in DAX can only evaluated at initial load (ignoring slicers and filter selections).
And you can't use SUMMARIZE(COLUMNS) in contextual measure (i.e. in most visuals use contextual evaluation, only card or single value visual would not have context).

If you can upload raw data I can work with it to show transformation steps.

 

[Chihiro]

Just for the heck of it, I've been playing with DAX version.

Currently in calculated table for testing purpose. You can see, how it's a bit convoluted (this is due to different aggregation level present on 2 columns [PuestoTrabajo] & [Material]).

Table =
VAR __aTbl =
    FILTER (
        fIDO,
        fIDO[Fecha] >= DATE ( 2019, 5, 1 )
            && fIDO[Fecha] <= DATE ( 2019, 5, 31 )
            && fIDO[PlantaKPI] = "Snacky"
    )
VAR __bTbl =
    GROUPBY (
        __aTbl,
        fIDO[PuestoTrabajo],
        fIDO[Material],
        "totKG", SUMX ( CURRENTGROUP (), fIDO[Entrada KG Suma] ),
        "Avg", AVERAGEX ( CURRENTGROUP (), fIDO[STD kg/h] ),
        "dur", SUMX ( CURRENTGROUP (), fIDO[Duración] ),
        "Calc", SUMX ( CURRENTGROUP (), fIDO[STD kg/h] * fIDO[Duración] )
    )
VAR __cTbl =
    GROUPBY (
        __bTbl,
        fIDO[Material],
        "Total KG Estándar Esperado Totalizando Mat", SUMX ( CURRENTGROUP (), [Calc] )
    )
VAR __dTbl =
    NATURALLEFTOUTERJOIN ( __bTbl, __cTbl )
VAR __eTbl =
    ADDCOLUMNS (
        __dTbl,
        "KG Real Puesto Trabajo por Mat", [Calc] / [Total KG Estándar Esperado Totalizando Mat] * [totKG]
    )
VAR __fTbl =
    GROUPBY (
        __eTbl,
        fIDO[PuestoTrabajo],
        "a", SUMX ( CURRENTGROUP (), [KG Real Puesto Trabajo por Mat] )
    )
RETURN
    __fTbl

Result.

fIDO_PuestoTrabajo	a
6702041	28766.5013638857
6702043	52223.9372787355
6702044	15617.5613573788

However, if you use this in measure, it's evaluated based on context and will return different result.
There may be a way, but I'll really have to think on that one.

 

[John Jairo V]

Sorry for the late answer. I was really busy this time.

I get the solution with this DAX formula:

IF(ISNUMBER([Total KG Produccion IDO]) ; SUMX ( CALCULATETABLE( dMaterial ; ALL(dMaterial) ) ; [KG Real Puesto Trabajo por Mat] ) ; BLANK())

Really thx for your support. Blessings!

 

[Chihiro]

Ah, I should have thought of calculatetable. Thanks for sharing your solution.

Glad you got it solved. Sorry I wasn’t much help.