Cantidad dividida entre departamentos

[Anonymous]

Hola

Necesita ayuda en la medida DAX. Cada línea de la tabla FACT contiene una cantidad, un departamento y una columna 'SplitID', que especifica entre los departamentos de brujas para dividir la cantidad. Por ejemplo. La cantidad para Dep 90-1 en januar es 150/500 * 10.000-3.000. Nb: En muchas líneas del FACTTrans real, SplitID es nulo y el importe total se asigna al departamento en la columna 'Dep'.

Debe ser posible, pero ¿cómo? 🐵 Saludos, Henrik

[camargos88]

Hola @hvpbi

He creado este archivo como ejemplo: Descargar PBIX

Ricardo

[Anonymous]

Gracias por el respose muy rápido.

Pero algo está mal - vea este ejemplo, donde su archivo resulta en 20.000 para cada departamento?

¿Quizás necesite usar sus dos columnas llamadas Key?

Este post no subiría mi foto, así que ella es el ejemplo en texto sin formato:

DIMSplit
Aaa	90-1	150
Aaa	90-2	150
Aaa	90-3	200
Bbb	90-1	50
Bbb	90-2	50

FACTTrans
01-01-2020	10.000	90-1	Aaa
01-01-2020	20.000	90-1	Aaa
01-01-2020	30.000	90-1	Bbb

Resultado esperado
90-1	24.000
90-2	24.000
90-3	12.000
	60.000

[camargos88]

@hvpbi ,

No entendí cómo conectaste tus mesas. Así que creé un puente con la columna ID y conecté ambas tablas en él.

El total de la fila for se calcula por:

Compartir: suma de acciones de Dep

Valor: suma si compartir por ID

Importe: suma de la cantidad por ID

Después de eso acabo de aplicar la fórmula (Compartir / Valor) * Importe.

Una vez que las tablas están conectadas por ID, el valor será el mismo para el total de filas.

Si esto no es correcto, con el fin de ayudarle necesito entender cómo lo está conectando.

Ricardo

[Anonymous]

@camargos88

Las relaciones entre los tabels forman parte de la pregunta 🐵

Y seguiré su consejo, muy agradecido.

He intentado con dos mesas de puente, identifica una clave, combinada con USERELATIONSHIP, pero sin suerte.

Aquí tienes un mejor ejemplo, donde

1. dep 90-1 tiene transacciones con dos ID de división diferenciados en enero
2. 90-1 tiene líneas sin ID de división(En muchas líneas en el FACTTrans real, SplitID es nulo, y el importe total se asigna al departamento en la columna 'Dep')

[Anonymous · Accepted solution]

Voy a dar una solución paso a paso a esto. Le sugiero que comience con un archivo de Power BI en blanco y comience desde cero para una mejor comprensión.

Paso 1: Agregue la tabla DIMSplit al modelo de datos mediante la opción "Nueva tabla" utilizando el siguiente código.

DIMSplit =
DATATABLE (
    "ID", STRING,
    "Dep", STRING,
    "Share", INTEGER,
    {
        { "aaa", "90-1", "150" },
        { "aaa", "90-2", "150" },
        { "aaa", "90-3", "200" },
        { "bbb", "90-1", "50" },
        { "bbb", "90-2", "50" }
    }
)

Paso 2: Agregue la tabla "FACTTrans" a su modelo de datos usando la opción "Nueva tabla" usando el siguiente código.

FACTTrans =
DATATABLE (
    "Date", DATETIME,
    "Amount", DOUBLE,
    "Dep", STRING,
    "SplitID", STRING,
    {
        { "01-01-2020", "10.000", "90-1", "aaa" },
        { "01-01-2020", "15.000", "90-1", "bbb" },
        { "01-01-2020", "25.000", "90-1", BLANK () },
        { "02-02-2020", "20.000", "90-1", "aaa" },
        { "03-03-2020", "30.000", "90-1", "bbb" }
    }
)

Paso 3: Agregue la tabla "Resultado esperado" a su modelo de datos mediante la opción "Nueva tabla" utilizando el siguiente código DAX.

ExpectedResult =
VAR SumTable =
    SUMMARIZECOLUMNS ( FACTTrans[Date], DIMSplit[Dep], DIMSplit[ID] )
VAR EmptyIDTable =
    GENERATE (
        SUMMARIZECOLUMNS ( 'FACTTrans'[Date], DIMSplit[Dep] ),
        ROW ( "ID", BLANK () )
    )
RETURN
    UNION ( SumTable, EmptyIDTable )

Paso 4: Agregue las siguientes columnas calculadas a la tabla "Resultado esperado" utilizando los siguientes códigos DAX.

Amt =
VAR CurrentDate = ExpectedResult[Date]
VAR CurrentDepartment = ExpectedResult[Dep]
VAR CurrentID = ExpectedResult[ID]
RETURN
    SUMX (
        FILTER (
            FACTTrans,
            FACTTrans[Dep] = CurrentDepartment
                && FACTTrans[Date] = CurrentDate
                && FACTTrans[SplitID] = CurrentID
        ),
        FACTTrans[Amount]
    )

DateIDAmount =
VAR CurrentDate = ExpectedResult[Date]
VAR CurrentID = ExpectedResult[ID]
VAR Sum1 =
    SUMX (
        FILTER (
            ExpectedResult,
            ExpectedResult[Date] = CurrentDate
                && ExpectedResult[ID] = CurrentID
        ),
        ExpectedResult[Amt]
    )
VAR Sum2 = ExpectedResult[Amt]
RETURN
    IF ( ISBLANK ( ExpectedResult[ID] ), Sum2, Sum1 )

PercentageSplit =
VAR CurrentID = ExpectedResult[ID]
VAR CurrentDep = ExpectedResult[Dep]
VAR DepIDAmount =
    SUMX (
        FILTER ( DIMSplit, DIMSplit[Dep] = CurrentDep && DIMSplit[ID] = CurrentID ),
        DIMSplit[Share]
    )
VAR IDAmount =
    SUMX ( FILTER ( DIMSplit, DIMSplit[ID] = CurrentID ), DIMSplit[Share] )
RETURN
    IF ( ISBLANK ( ExpectedResult[ID] ), 1, DIVIDE ( DepIDAmount, IDAmount, 0 ) )

FinalAmount =
ExpectedResult[DateIDAmount] * ExpectedResult[PercentageSplit]

Paso 5: Agregue un objeto visual de matriz a su informe y use los campos date(month) y Department de la tabla Desestimada de resultados esperados a la columna y las filas respectivamente y agregue el campo "Importe final" a la sección "Valores" de la matriz, lo que le dará el siguiente resultado.

Esta no es una solución óptima, pero sin embargo obtendrá los resultados y también se pueden ver los resultados intermedios en cada paso. Más adelante, puede simplificar los códigos o reescribir los mismos utilizando medidas que consumirán mucho la energía de la CPU si el conjunto de datos es bastante grande.

Por favor, hágamelo saber si tiene algún problema para entender cualquiera de los fragmentos de código DAX. Por cierto, no hay relaciones entre ninguna de estas tablas.

[Anonymous]

@sreenathv

Impresionante.

Estudiaré esto cuidadosamente.

Cuando escribes Esto no es una solución óptima, ¿crees que performace?

Es importante para mí pensar en el rendimiento, debido a grandes conjuntos de datos, y puede haber un problema con

• el uso de tablas temporales
• el uso de SUMX(FILTER ...)
• no uso de relaciones

?

respectos, Henrik

[Anonymous]

En cuanto a mi descargo de responsabilidad sobre la optimización del código, podemos lograr los resultados con menos y más eficiente código si nuestro modelo de datos es correcto.

1) Por ejemplo, cada vez que hay un análisis relacionado con la fecha/hora, casi siempre lo hace, mantengo una tabla de "Calendario" en mis modelos de datos y la marco como una tabla de fechas. Antes de eso, desactivo la "Fecha/Hora automática" en el menú Archivo->Opciones y ajustes->Opciones-> Carga de datos del archivo. Hay una razón para hacerlo, que es bastante elaborada para explicar aquí.

2) Hay una relación de varios a varios en su archivo - El SplitID en las tablas de hechoy dim. Por lo general, para manejar esto, necesitamos una mesa de puente.

3) Para un mejor rendimiento, si no hay ningún predicado de varias columnas en FILTER que devuelva una tabla, podemos usar la función CALCULATETABLE que es mejor en términos de rendimiento. Pero cada vez que hay un CALCULATE / CALCULATETABLE, hay más cosas a considerar como transiciones de contexto, reutilización de código / medida, etc...

4) Si tenemos un modelo de datos adecuado con las relaciones correctas entre las tablas, la cantidad de código DAX necesaria para lograr resultados es considerablemente menor.

5) Sin un conocimiento completo del modelo de datos y el volumen de registros, generalmente, el primer paso es resolver el desafío paso a paso y no preocuparse por la optimización. Una vez que tengamos los resultados correctos y con una comprensión adecuada del modelo de datos completo, podemos optimizar y simplificar aún más - esta parte sólo usted puede hacer.

[Anonymous]

@sreenathv

Gracias por la gran explicación.

Tengo un calendario de fechas en mi modelo, y FACTTrans creo que será menos de 1 mio.

Puedo hacer una mesa de brigada si es necesario.

Si usted puede guiarme a la tabla de puente necesario y me guía en la dirección del código óptimo, que sería genial.

Voy a estudiar su paso a paso de todos modos, seguro 🙂

saludos

Henrik

[Anonymous]

Hola @hvpbi

Mediante una tabla de puente pequeña, puede lograr los resultados utilizando una medida en sí misma en lugar de utilizar la tabla calculada "Resultado esperado" y las otras "columnas calculadas" de esa tabla.

La 2a solución que utiliza la medida es la siguiente...

Requisito previo: abra un archivo de Power BI en blanco y complete los pasos 1 y 2 mencionados anteriormente para crear las tablas "DIMSplit" y "FACTTrans".

Paso 3: Crear una tabla de puentes: use la tabla calculada / Nueva tabla con el siguiente código DAX.

IDs_BridgeTable = ALL(DIMSplit[ID])

Paso 4: Cree las siguientes relaciones.

IDs_BridgeTable[ID] -> DIMSplit[ID], One to Many, Relación activa

IDs_BridgeTable[ID] -> FACTTrans[SplitID], One to Many, Relación activa

DIMSplit[Dep] <-> FACTTrans[Dep], Muchos a muchos, relación inactiva

Paso 5: Crear una medida

ExpectedResult =
VAR IT_TABLE =
    ADDCOLUMNS (
        SUMMARIZE ( DIMSplit, DIMSplit[ID], DIMSplit[Dep] ),
        "ID_Dep_Amount", CALCULATE (
            SUMX ( FACTTrans, FACTTrans[Amount] ),
            CROSSFILTER ( DIMSplit[ID], IDs_BridgeTable[ID], BOTH )
        ),
        "ID_Total", CALCULATE ( SUMX ( DIMSplit, DIMSplit[Share] ), ALL ( DIMSplit[Dep] ) ),
        "ID_Dep_Share", CALCULATE ( SUMX ( DIMSplit, DIMSplit[Share] ) )
    )
VAR Res1 =
    SUMX ( IT_TABLE, [ID_Dep_Amount] * DIVIDE ( [ID_Dep_Share], [ID_Total], 0 ) )
VAR Res2 =
    CALCULATE (
        SUMX ( FILTER ( FACTTrans, FACTTrans[SplitID] = "" ), FACTTrans[Amount] ),
        USERELATIONSHIP ( DIMSplit[Dep], FACTTrans[Dep] )
    )
VAR Result = Res1 + Res2
RETURN
    Result

Paso 6: Utilice un objeto visual de matriz y use DIMSplit[Dep] en Rows, FACTTrans[Date]. Mes en columnas y la medida "Resultado esperado" creada en el paso 5 en la sección "Valores" de la matriz para obtener el siguiente resultado.

La principal diferencia entre la solución anterior y esta es que la anterior se basa en tablas calculadas y columnas calculadas creadas con DAX y el resultado se almacena en el modelo de datos en una tabla independiente. Pero en este caso, el resultado se calcula utilizando solo una medida sin necesidad de ninguna tabla calculada o columnas calculadas, excepto para la tabla de puentes en muchas a muchas relaciones. Podríamos haber evitado la mesa del puente también, pero hará que la medida sea más complicada.

Puede probar ambos enfoques en el modelo de datos y usar "Análisis de rendimiento" para evaluar y comparar el rendimiento entre los enfoques y su diferencia será evidente si utiliza un conjunto de datos realmente grande. Depende de usted decidir entre los dos enfoques.

[Anonymous]

Impresionante, muchas gracias 🐵