Solved: Medidas semi-aditivas o cómo ignorar solo una sele...

Syndicate_Admin · ‎10-19-2021

Hola

Tengo un problema al crear una medida que calcule correctamente (incluida la fila total) y haga que ignore una sola selección de segmentación de datos (pero manteniendo las demás).

La definición de la tabla "factCustomers" es:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rZU/b8IwEMW/CmJqJQedfed/3Tp06NTuiAHRDJFoQChF4ts3cew2KBejliyRowf+6Z7fi9frJRkw3i7F8rWummq7X7zUzenSvitYgV0pUHIB8AQQfnI+VLuyXQGtwF2LyoNQrluY9vF2Lk8fX+XiQRYIj7/yRnRIZZzhkJhD2hHSoJbCy/BPjpn0CLUwB9SiFuQnmVEOSESpNYPEnLXKj5DSaOGoW3DEqCaik3MQveyH5Im9Gok2nPq9RHJKCZocMsmBSdJb+itTjiNrSAkTHGTTE9Q+O+RRzQCU4FBoN4lMeu8sacOlB3C07/v28lnWDS8WbduECi2xHDTp6TglFyCm8INJx8dpncBuH8meZq9Ga5XkagnjPQf5YaxFdMJ2fvHORjkxcQ6mRRJkppBRTQHSXIDk+LgGRDcSEcmKkH3FIZOcmA5nYBKJ8LVmib0YWwmTF8lkXhmx+MkOsHEdhCdV5Pl43Fe7bVMd6n8U5EY/uHpcA5khsuUo8u0YTphMvQnMWpp3dGhovLGucZibj/mWF/kLq+BurHuJ2QsryZvNNw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Entry_No = _t, Entry_Type = _t, Posting_Date = _t, Document_Type = _t, Initial_Entry_Due_Date = _t, Amount_LCY = _t, #"Customer PastDue" = _t, #"Age Group" = _t, #"Customer Sold per Aging Grup" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Entry_No", Int64.Type}, {"Entry_Type", type text}, {"Posting_Date", type datetime}, {"Document_Type", type text}, {"Initial_Entry_Due_Date", type datetime}, {"Amount_LCY", type number}, {"Customer PastDue", Int64.Type}, {"Age Group", type text}, {"Customer Sold per Aging Grup", type number}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type)
in
    #"Added Index"

Y se verá así:

Esta tabla contiene las facturas y/o pagos con Entry_Type = "Entrada Inicial" y la aplicación Navision añadirá automáticamente los registros corresponting con el mismo Entry_No pero el Entry_Type = "Aplicación" cuando los valores se "deduzcan"/paguen.

Sobre la base de Initial_Entry_Due_Date se calculó en la fecha seleccionada (30.08.2021) qué facturas son PastDue para ver qué tan tarde están los pagos: en la tabla anterior, el PastDue del cliente se usó como "valores", pero en el informe real es de hecho una medida (pero no creo que esto esté influyendo en mis siguientes medidas).

Ahora estoy obligado a calcular algunas medidas.

La primera, muy sencilla:

mx Original Amount = 
CALCULATE (
    SUM ( factCustomers[Amount_LCY] ),
    KEEPFILTERS ( factCustomers[Entry_Type] = "Initial Entry" )
)

Esta medida debe mostrar los valores de Amount_LCY solo donde [Entry_Type] = "Entrada inicial".

El segundo debe calcular la cantidad restante, sumando el Amount_LCY, para el mismo Entry_No, ignorando el Entry_Type:

mx Remaining Amount = 
VAR vEntryNo =
    MAX ( factCustomers[Entry_No] )
VAR vEntryType =
    MAX ( factCustomers[Entry_Type] )
VAR vResult =
    IF (
        vEntryType <> "Initial Entry",
        BLANK (),
        CALCULATE (
            SUM ( factCustomers[Amount_LCY] ),
            FILTER (
                ALL ( factCustomers ),
                factCustomers[Entry_No] = vEntryNo
                    && factCustomers[Posting_Date] <= MAX ( dimDate[Date] )
                    && factCustomers[Initial_Entry_Due_Date] <= MAX ( dimDate[Date] )
            )
        )
    )
RETURN
    ROUND ( vResult, 2 )

Más exactamente para este par de registros:

Entry_No	Entry_Type	Posting_Date	Document_Type	Initial_Entry_Due_Date	Amount_LCY
433155	Entrada inicial	30.07.2021	Factura	29.08.2021	165.84
433155	Aplicación	30.08.2021	Factura	29.08.2021	-165.84

el importe restante debe ser 0, pero el valor debe mostrarse sólo en las filas con [Entry_Type] = "Entrada inicial".

El problema con esta medida es el total incorrecto (muestra solo el último valor no en blanco o cero) en lugar de agregar todos los valores mostrados para las filas:

Para corregir el valor total he intentado con una segunda medida:

mx Remaining Amount2 = 
VAR vEntryNo =
    MAX ( factCustomers[Entry_No] )
VAR vEntryType =
    MAX ( factCustomers[Entry_Type] )
VAR vResult =
    ROUND (
        CALCULATE (
            SUM ( factCustomers[Amount_LCY] ),
            FILTER (
                ALL ( factCustomers ),
                factCustomers[Entry_No] = vEntryNo
                    && factCustomers[Posting_Date] <= MAX ( dimDate[Date] )
                    && factCustomers[Initial_Entry_Due_Date] <= MAX ( dimDate[Date] )
            )
        ),
        2
    )
VAR vTotalResult =
    ROUND (
        CALCULATE ( SUM ( factCustomers[Amount_LCY] ), ALLSELECTED ( factCustomers ) ),
        2
    )
RETURN
    IF (
        HASONEVALUE ( factCustomers[Entry_No] ),
        IF ( vEntryType = "Initial Entry", vResult, BLANK () ),
        vTotalResult
    )

Esto es casi lo que necesito, excepto el hecho de que al seleccionar en la segmentación de datos solo la opción"Entrada inicial"para deshacerse de los registros de tipo "Aplicación", me gustaría mostrar el total de 19.392,11 como en la imagen inicial, no 19.481,23 como en la imagen de abajo.

Entonces, ¿es posible hacer cualquiera de las dos versiones para "Cantidad restante" para calcular el total de los valores mostrados? - porque las filas seguirán mostrando los valores adecuados - el total es mi problema.

Para mayor comodidad, el informe completo está disponible (durante 30 días, debido a la política de uso compartido) como PBIX aquí: MeasureTotals.pbix

Atentamente

Lucian

Syndicate_Admin · ‎10-20-2021

Sí, no estaba muy seguro de cómo se suponía que la fecha entraría en juego, así que pensé que comenzaría de manera simple y luego agregaría complejidad según fuera necesario.

Si tiene una relación activa, por lo que dimDate[Date] filtra factCustomers[Initial_Entry_Due_Date], entonces solo necesita agregar dimDate[Date] a ALLSELECTED.

mx Remaining Amount = 
IF (
    "Initial Entry" IN VALUES ( factCustomers[Entry_Type] ),
    CALCULATE (
        SUM ( factCustomers[Amount_LCY] ),
        ALLEXCEPT ( factCustomers, factCustomers[Entry_No], dimDate[Date] )
    )
)

Si no quieres relaciones activas, entonces tienes que hacer un poco más de trabajo. Activar la relación en la medida

mx Remaining Amount = 
IF (
    "Initial Entry" IN VALUES ( factCustomers[Entry_Type] ),
    CALCULATE (
        SUM ( factCustomers[Amount_LCY] ),
        ALLEXCEPT ( factCustomers, factCustomers[Entry_No], factCustomers[Initial_Entry_Due_Date] ),
        USERELATIONSHIP( factCustomers[Initial_Entry_Due_Date], dimDate[Date] )
    )
)

O bien aplicar el filtro un poco más manualmente como este

mx Remaining Amount = 
IF (
    "Initial Entry" IN VALUES ( factCustomers[Entry_Type] ),
    CALCULATE (
        SUM ( factCustomers[Amount_LCY] ),
        ALLEXCEPT ( factCustomers, factCustomers[Entry_No] ),
        factCustomers[Initial_Entry_Due_Date] IN VALUES ( dimDate[Date] )
    )
)

o esto

mx Remaining Amount =
VAR MaxDate = MAX ( dimDate[Date] )
RETURN
    IF (
        "Initial Entry" IN VALUES ( factCustomers[Entry_Type] ),
        CALCULATE (
            SUM ( factCustomers[Amount_LCY] ),
            ALLEXCEPT ( factCustomers, factCustomers[Entry_No] ),
            factCustomers[Initial_Entry_Due_Date] <= MaxDate
        )
    )

View solution in original post

Syndicate_Admin · ‎10-19-2021

Puede usar ALLEXCEPT para eliminar cualquier filtro excepto Entry_No.

¿Qué tal algo mucho más simple como esto?

mx Remaining Amount =
IF (
    "Initial Entry" IN VALUES ( factCustomers[Entry_Type] ),
    CALCULATE (
        SUM ( factCustomers[Amount_LCY] ),
        ALLEXCEPT ( factCustomers, factCustomers[Entry_No] )
    )
)

Syndicate_Admin · ‎10-20-2021

Hay @AlexisOlson

Primero me gustaría agradecerle por su tiempo dedicado a mi problema, y me sorprende lo "ligera" que es su fórmula. 😲

A primera vista me emocioné porque parecía funcionar perfectamente, pero luego me he dado cuenta de que olvidé mencionar que el "Valor Restante" debe calcularse en función de la "Fecha Seleccionada".

Así que tengo que disculparme por este "pequeño detalle faltante", que causaría un problema al seleccionar otra fecha como el 27 de agosto:

En este caso, mis fórmulas (cualquiera de las dos) no mostrarán el "Importe restante" en las filas que "no vencen" en la fecha seleccionada. Debido a mi información errónea, su fórmula llamada "mx Remaining Amount3" en la imagen de arriba devolverá resultados incorrectos en las filas y, por supuesto, el total también será incorrecto. En la nueva fecha seleccionada el total debería ser de 11.177,07.

¿Podría decirme cómo podría resolver el total en este caso?