Incorrect Matrix visual subtotal

ck1995 · ‎04-08-2022

I have a table , that contains sales price of products with unique serial key month wise. I am trying to calculate the cost of material overrun between two months in matrix visual , the subtotal is always zero unless filtered. But the same formula after the Return statement gets calculated in excel pivot table. I have created a dummy data for reference.. And the dax i created for the measure.

Serial number	File date	Data file month&year	Estimate profit (EP)	Actual cost(AP)	Diff in EP & AP (Created a calculated column in table)
A11	01 January 2022	202201	100	110	10
A22	01 January 2022	202201	200	-190	-390
A33	01 January 2022	202201	300	310	10
A44	01 January 2022	202201	400	410	10
A55	01 January 2022	202201	500	-510	-1010
A11	01 February 2022	202202	90	100	10
A22	01 February 2022	202202	190	200	10
A33	01 February 2022	202202	290	300	10
A44	01 February 2022	202202	390	400	10
A55	01 February 2022	202202	-490	-480	10
A11	01 March 2022	202203	80	90	10
A22	01 March 2022	202203	180	190	10
A33	01 March 2022	202203	280	290	10
A44	01 March 2022	202203	380	390	10
A55	01 March 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))))

Matrix o/p

Anonymous · ‎04-13-2022

Hi @ck1995 ,

Are there two tables 'Data' and 'Datatable' in your measure? I see in var defaultmonth you use 'Data' and in below codes you use 'Datatable'. And I couldn't find [Current Month] in your sample. I see you get result by IF function, if you want to get correct subtotal in visual, there are two ways.

1. Calculate based on measure directly.

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

2. Create a virtual table when you create the measure.

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] )

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

ck1995 · ‎04-13-2022

@Anonymous Sorry for the confusion betweeen datatable and data , it is the same table, that was my typo error. Also current month column is "Data file month&year".

I tried the solution , but the value returned is 0 throughout the table.

Anonymous · ‎04-14-2022

Hi @ck1995 ,

I create a sample like yours, I meet another problem is that I think you are creating a matrix, which column is in matrix row field? I have tried [Serial number] or [File date]. It seems that [Material Overrun] will always return 0 in all rows.

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 )
            )
        )
    )

Result is as below. If all rows return 0, subtotal will show 0 as well.

Please make sure [Material Overrun] will return correct results in other rows. You show me more details about what result you want.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

ck1995 · ‎04-14-2022

Hi @Anonymous Serial number is in the rows. Is there any way for us to connect so that I could elaborate as the dataset is a large file and this is a replica of the dataset that I am working on and cannot be shared

Anonymous · ‎04-15-2022

Hi @ck1995 ,

Sorry, I could only work with you in Community. Will [Material Overrun] return 0 for all Serial number in your side? If your result is like me, I think there should be something wrong in your measure logic. You can tell me your calculate logic and the result you want.

If not, I think the result is based on the sample data above, you can update the sample to let measure not show 0 for all Serial numbers.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.