Subtotal visual de Matrix incorrecto

Syndicate_Admin · ‎04-08-2022

Tengo una tabla, que contiene el precio de venta de los productos con clave de serie única en cuanto al mes. Estoy tratando de calcular el costo del exceso de material entre dos meses en la matriz visual, el subtotal es siempre cero a menos que se filtre. Pero la misma fórmula después de la instrucción Return se calcula en la tabla dinámica de Excel. He creado un dato ficticio para referencia. Y el dax que creé para la medida.

Número de serie	Fecha del archivo	Archivo de datos mes&año	Beneficio estimado (EP)	Costo real (AP)	Diff en EP & AP (Se creó una columna calculada en la tabla)
A11	01 enero 2022	202201	100	110	10
A22	01 enero 2022	202201	200	-190	-390
A33	01 enero 2022	202201	300	310	10
A44	01 enero 2022	202201	400	410	10
A55	01 enero 2022	202201	500	-510	-1010
A11	01 febrero 2022	202202	90	100	10
A22	01 febrero 2022	202202	190	200	10
A33	01 febrero 2022	202202	290	300	10
A44	01 febrero 2022	202202	390	400	10
A55	01 febrero 2022	202202	-490	-480	10
A11	01 marzo 2022	202203	80	90	10
A22	01 marzo 2022	202203	180	190	10
A33	01 marzo 2022	202203	280	290	10
A44	01 marzo 2022	202203	380	390	10
A55	01 marzo 2022	202203	-500	-490	10

Material Overrun = var defaultmonth =  VALUE(max(data[File date].[Year]) & FORMAT(eomonth(TODAY(),-1), "mm"))
var _max = CALCULATE(MAX((Datatable[Current Month])), ALLSELECTED(Datatable[Current Month]))

var _EPdefault = SUMX(filter(Datatable,Datatable[Current Month] = defaultmonth),sum('Datatable'[Diff in EP & AP (MAT)]))
var _EPmax = SUMX(filter(Datatable,Datatable[Current Month] IN {_max}),sum('Datatable'[Diff in EP & AP (MAT)]))
var EPAP = SUMX(Datatable,if(ISFILTERED(Datatable[Current Month]),_EPmax,_EPdefault))

var defmonth =  VALUE(max(Datatable[File date].[Year]) & FORMAT(eomonth(TODAY(),-2), "mm"))
var _minmnth =  if(isfiltered(Datatable[Current Month]),CALCULATE(Min((Datatable[Current Month])), ALLSELECTED(Datatable[Current Month])),defmonth)
VAR __BASELINE_VALUE = SUMX(filter(Datatable,Datatable[Current Month] IN {_minmnth}),sum('Datatable'[Actual cost]))

var _maxmnth =  if(isfiltered(Datatable[Current Month]),_max,defaultmonth)
VAR __MEASURE_VALUE = SUMX(filter(Datatable,Datatable[Current Month] IN {_maxmnth}),sum('Datatable'[Actual cost]))
var changeinmat = __MEASURE_VALUE - __BASELINE_VALUE

RETURN
IF(value(EPAP)>0,0,if( value(changeinmat) < 0,0,IF(value(0-changeinmat )>(value(EPAP)),value(0-changeinmat), value(EPAP))))

Matriz o/p

Syndicate_Admin · ‎04-13-2022

Hay @ck1995 ,

¿Hay dos tablas 'Datos' y 'Datatable' en su medida? Veo que en var defaultmonth usas 'Data' y en los códigos de abajo usas 'Datatable'. Y no pude encontrar [Mes actual] en su muestra. Veo que obtienes resultado por función IF, si quieres obtener el subtotal correcto en visual, hay dos formas.

1. Calcule en función de la medida directamente.

Material Overrun with correct total = 
SUMX(VALUES(Table[Column in matrix Rows]),[Material Overrun])

2. Cree una tabla virtual al crear la medida.

Material Overrun =
VAR _VIRTUAL_TABLE =
    SUMMARIZE (
        ALL ( TABLE ),
        TABLE[COLUMN IN MATRIX ROWS],
        "Material Overrun",
            VAR defaultmonth =
                VALUE (
                    MAX ( Data[File date].[Year] ) & FORMAT ( EOMONTH ( TODAY (), -1 ), "mm" )
                )
            VAR _max =
                CALCULATE (
                    MAX ( Datatable[Current Month] ),
                    ALLSELECTED ( Datatable[Current Month] )
                )
            VAR _EPdefault =
                SUMX (
                    FILTER ( Datatable, Datatable[Current Month] = defaultmonth ),
                    SUM ( 'Datatable'[Diff in EP & AP (MAT)] )
                )
            VAR _EPmax =
                SUMX (
                    FILTER ( Datatable, Datatable[Current Month] IN { _max } ),
                    SUM ( 'Datatable'[Diff in EP & AP (MAT)] )
                )
            VAR EPAP =
                SUMX (
                    Datatable,
                    IF ( ISFILTERED ( Datatable[Current Month] ), _EPmax, _EPdefault )
                )
            VAR defmonth =
                VALUE (
                    MAX ( Datatable[File date].[Year] ) & FORMAT ( EOMONTH ( TODAY (), -2 ), "mm" )
                )
            VAR _minmnth =
                IF (
                    ISFILTERED ( Datatable[Current Month] ),
                    CALCULATE (
                        MIN ( ( Datatable[Current Month] ) ),
                        ALLSELECTED ( Datatable[Current Month] )
                    ),
                    defmonth
                )
            VAR __BASELINE_VALUE =
                SUMX (
                    FILTER ( Datatable, Datatable[Current Month] IN { _minmnth } ),
                    SUM ( 'Datatable'[Actual cost] )
                )
            VAR _maxmnth =
                IF ( ISFILTERED ( Datatable[Current Month] ), _max, defaultmonth )
            VAR __MEASURE_VALUE =
                SUMX (
                    FILTER ( Datatable, Datatable[Current Month] IN { _maxmnth } ),
                    SUM ( 'Datatable'[Actual cost] )
                )
            VAR changeinmat = __MEASURE_VALUE - __BASELINE_VALUE
            RETURN
                IF (
                    VALUE ( EPAP ) > 0,
                    0,
                    IF (
                        VALUE ( changeinmat ) < 0,
                        0,
                        IF (
                            VALUE ( 0 - changeinmat )
                                > ( VALUE ( EPAP ) ),
                            VALUE ( 0 - changeinmat ),
                            VALUE ( EPAP )
                        )
                    )
                )
    )
RETURN
    SUMX ( _VIRTUAL_TABLE, [Material Overrun] )

Saludos
Rico Zhou

Si esta publicación ayuda, considere Aceptarla como la solución para ayudar a los otros miembros a encontrarla más rápidamente.

Syndicate_Admin · ‎04-13-2022

@RicoZhou Perdón por la confusión entre datatable y data, es la misma tabla, ese fue mi error tipográfico. También la columna del mes actual es "Archivo de datos mes y año".

Probé la solución, pero el valor devuelto es 0 en toda la tabla.

Syndicate_Admin · ‎04-14-2022

Hay @ck1995 ,

Creo una muestra como la tuya, me encuentro con otro problema es que creo que estás creando una matriz, ¿qué columna está en el campo de fila de matriz? He probado [Número de serie] o [Fecha del archivo]. Parece que [Material Overrun] siempre devolverá 0 en todas las filas.

Material Overrun = 
VAR _defaultmonth =
    VALUE (
        YEAR ( MAX ( 'Datatable'[File date] ) )
            & FORMAT ( EOMONTH ( TODAY (), -1 ), "mm" )
    )
VAR _max =
    CALCULATE (
        MAX ( ( 'Datatable'[Data file month&year] ) ),
        ALLSELECTED ( 'Datatable'[Data file month&year] )
    )
VAR _EPdefault =
    SUMX (
        FILTER ( 'Datatable', 'Datatable'[Data file month&year] = _defaultmonth ),
        SUM ( 'Datatable'[Diff in EP & AP (MAT)] )
    )
VAR _EPmax =
    SUMX (
        FILTER ( 'Datatable', 'Datatable'[Data file month&year] IN { _max } ),
        SUM ( 'Datatable'[Diff in EP & AP (MAT)] )
    )
VAR EPAP =
    SUMX (
        'Datatable',
        IF ( ISFILTERED ( 'Datatable'[Data file month&year] ), _EPmax, _EPdefault )
    )
VAR defmonth =
    VALUE (
        YEAR ( MAX ( 'Datatable'[File date] ) )
            & FORMAT ( EOMONTH ( TODAY (), -2 ), "mm" )
    )
VAR _minmnth =
    IF (
        ISFILTERED ( 'Datatable'[Data file month&year] ),
        CALCULATE (
            MIN ( ( 'Datatable'[Data file month&year] ) ),
            ALLSELECTED ( 'Datatable'[Data file month&year] )
        ),
        defmonth
    )
VAR __BASELINE_VALUE =
    SUMX (
        FILTER ( 'Datatable', 'Datatable'[Data file month&year] IN { _minmnth } ),
        SUM ( 'Datatable'[Actual cost(AP)] )
    )
VAR _maxmnth =
    IF ( ISFILTERED ( 'Datatable'[Data file month&year] ), _max, _defaultmonth )
VAR __MEASURE_VALUE =
    SUMX (
        FILTER ( 'Datatable', 'Datatable'[Data file month&year] IN { _maxmnth } ),
        SUM ( 'Datatable'[Actual cost(AP)] )
    )
VAR changeinmat = __MEASURE_VALUE - __BASELINE_VALUE
RETURN
    IF (
        VALUE ( EPAP ) > 0,
        0,
        IF (
            VALUE ( changeinmat ) < 0,
            0,
            IF (
                VALUE ( 0 - changeinmat )
                    > ( VALUE ( EPAP ) ),
                VALUE ( 0 - changeinmat ),
                VALUE ( EPAP )
            )
        )
    )

El resultado es el siguiente. Si todas las filas devuelven 0, el subtotal también mostrará 0.

Asegúrese de que [Exceso de material] devuelva los resultados correctos en otras filas. Me muestras más detalles sobre qué resultado quieres.

Saludos
Rico Zhou

Si esta publicación ayuda, considere Aceptarla como la solución para ayudar a los otros miembros a encontrarla más rápidamente.

Syndicate_Admin · ‎04-15-2022

Hola @RicoZhou número de serie está en las filas. ¿Hay alguna forma de que nos conectemos para poder elaborar ya que el conjunto de datos es un archivo grande y esta es una réplica del conjunto de datos en el que estoy trabajando y no se puede compartir?