Totales ponderados al asignar costos

[MG86]

Hola

(Ejemplo simplificado:) Tengo una tabla (Mutaciones financieras) con un Importe que es Ingresos o Costos. Cada mutación está en una fila en la tabla. Las filas de ingresos se comparan con un cliente. Sin embargo, los costes no siempre corresponden a un único cliente y no tienen ningún Cliente asignado en la fila.

Creé dos medidas. Uno es Ingresos que es CALCULATE(SUM(Amount),Revenue or Cost - "Revenue") y el otro es Costo que es la misma fórmula pero luego Coste.

Quiero asignar los costos no asignados a los Clientes, en función de los Ingresos.

Para ello creé una fórmula (CostAllocationBasedOnRevenue) que primero divide los ingresos por los ingresos totales y, a continuación, los multiplica con la suma de los costes de todas las filas en las que el cliente está en blanco:

CostAllocationBasedOnRevenue á DIVIDE([Revenue],CALCULATE([Revenue],ALL('Financial Mutations'[Customer]))) * CALCULATE([Cost], 'Financial Mutations'[Customer] á BLANK())

Esto básicamente funciona. Pero el problema es que con esta fórmula, el Total para el Cliente no se pondera para la Categoría, que tiene que ser.

Se muestra en una matriz:

Los importes individuales de las categorías son los que quiero, pero no suman "correctamente" (entiendo que Power BI técnicamente solo divide el total del cliente por el total general y devuelve un resultado correcto en ese sentido).

Así, por ejemplo, ¿cómo consigo que el cliente A total AllocatedCosts muestre 31.22 (5.77 + 25.45) en la línea de subtotal en matrix, en lugar de 28.81?

Intenté varias cosas como un SUMX en un resumen de Categoría y Cliente, pero no tuve suerte hasta un punto en el que pasé demasiadas horas y perdiendo de vista todo. ¿Qué me lleva a preguntarme si alguien podría ayudarme?

[MG86]

Aquí está el archivo de ejemplo PBIX (enlace válido para una semana):

https://we.tl/t-pmc1lH0syO

---

[daxer-almighty]

Sus cálculos son erróneos. Echa un buen vistazo a ellos arriba... Aquí hay un enlace a un archivo con cálculos correctos: https://1drv.ms/u/s!ApyQEauTSLtOgY8r0SeAOUgilUglFA?e=Gwl4HB

Por eso se equivocan. El cliente C tiene 2 categorías con los mismos ingresos pero diferentes costes asignados. Esto, por supuesto, está mal. Los costes deben ser los mismos, ya que los porcentajes de los ingresos totales de esas dos entradas son los mismos.

[MG86]

Hola daxer-todopoderoso,

Gracias. Sin embargo, el objetivo no es asignar en función del total general solamente. Si se conoce la categoría de los costes no asignados, deben distribuirse entre la categoría. Así que los costos asignados que tenía en el nivel de categoría eran correctos. Los totales a nivel de cliente deben ponderarse en consecuencia.

[MG86]

Creo que lo tengo. Usé su ejemplo de agregar una columna calculada que indica si los costos no están asignados (TRUE/FALSE) y luego usé esta medida:

AllocatedCosts2 =
CALCULATE (
    SUMX (
        'Financial Mutations',
        DIVIDE (
            CALCULATE (
                SUM ( 'Financial Mutations'[Amount] ),
                'Financial Mutations'[Revenue or Cost] = "Revenue"
            ),
            CALCULATE (
                SUM ( 'Financial Mutations'[Amount] ),
                ALLEXCEPT ( 'Financial Mutations', 'Financial Mutations'[Category] ),
                'Financial Mutations'[Revenue or Cost] = "Revenue"
            )
        )
            * CALCULATE (
                SUM ( 'Financial Mutations'[Amount] ),
                ALLEXCEPT ( 'Financial Mutations', 'Financial Mutations'[Category] ),
                'Financial Mutations'[Revenue or Cost] = "Cost",
                'Financial Mutations'[Unassigned]
            )
    )
)

De esta manera, la medida itera primero los ingresos y los divide por ingresos totales para la categoría. A continuación, lo multiplica por el total de costes no asignados para esa categoría. Entonces lo resume todo.

¿Es esta una solución adecuada?
Editar; Luchando con conseguir que funcione en mi modelo más complicado de la vida real. ¿Es correcta la teoría?

[daxer-almighty · Accepted solution]

Aquí está la medida que hace lo que usted quiere:

Allocated Cost = 
SUMX(
    DISTINCT( 'Financial Mutations'[Category] ),
    var __unallocatedCostForCategory = 
        CALCULATE(
            SUM( 'Financial Mutations'[Amount] ),
            ALLEXCEPT( 
                'Financial Mutations',
                'Financial Mutations'[Category]
            ),
            'Financial Mutations'[Amount Unassigned]
        )
    var __totalRevenueForCategory =
        CALCULATE(
            [Revenue],
            ALLEXCEPT( 
                'Financial Mutations',
                'Financial Mutations'[Category]
            )
        )
    var __revenueForCategory = [Revenue]
    var __percentage = 
        DIVIDE( 
            __revenueForCategory,
            __totalRevenueForCategory
        )
    var __result =
        __percentage * __unallocatedCostForCategory
    return
        __result
)

También he actualizado el archivo. Utilice el mismo enlace que el anterior para recuperarlo.

No se debe utilizar la solución. No debe escribir DAX de esta manera, ya que es extremadamente ineficiente. Nunca debe iterar las tablas de hechos fila por fila. Nunca. Hay demasiadas razones para afirmar. Si quieres saber por qué es un no-no, por favor lee el libro "La Guía Definitiva de DAX" de Marco Russo y Alberto Ferrari. Las tablas de hechos iteradas son de hecho tablas expandidas y esto no sólo pone mucha tensión en el motor de cálculo, sino que también hace que el código sea propenso a errores. Y además de eso, si hay varias filas SAME en la tabla se tratan como una y la misma fila. Esto puede introducir errores sutiles que nunca será capaz de detectar.

[MG86]

Hola

Gracias por toda la ayuda. Funciona como un encanto en los datos de muestra proporcionados. Todavía luchando con datos reales, pero esto me lleva más lejos.

Editar: Se metió en otro problema que había publicado aquí, pero que fue un simple error de mi lado.