Incorrect Matrix visual subtotal

Question

&nbsp;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 numberFile dateData file month&amp;yearEstimate profit (EP)Actual cost(AP)Diff in EP &amp; AP (Created a calculated column in table)A1101 January 202220220110011010A2201 January 2022202201200-190-390A3301 January 202220220130031010A4401 January 202220220140041010A5501 January 2022202201500-510-1010A1101 February 20222022029010010A2201 February 202220220219020010A3301 February 202220220229030010A4401 February 202220220239040010A5501 February 2022202202-490-48010A1101 March 2022202203809010A2201 March 202220220318019010A3301 March 202220220328029010A4401 March 202220220338039010A5501 March 2022202203-500-49010&nbsp;Material Overrun = var defaultmonth =  VALUE(max(data[File date].[Year]) &amp; 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 &amp; AP (MAT)]))
var _EPmax = SUMX(filter(Datatable,Datatable[Current Month] IN {_max}),sum('Datatable'[Diff in EP &amp; AP (MAT)]))
var EPAP = SUMX(Datatable,if(ISFILTERED(Datatable[Current Month]),_EPmax,_EPdefault))

var defmonth =  VALUE(max(Datatable[File date].[Year]) &amp; 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)&gt;0,0,if( value(changeinmat) &lt; 0,0,IF(value(0-changeinmat )&gt;(value(EPAP)),value(0-changeinmat), value(EPAP))))
​&nbsp;&nbsp;Matrix o/p&nbsp;

mahoneypat · Answer

Please see this video for how to get the expected totals.
(2) Power BI - Tales from the front #01 - Getting the Right Total - YouTube
Pat

anonymous · Answer

Hi&nbsp;ck1995&nbsp;,
&nbsp;
Are there two tables 'Data' and 'Datatable' in your measure? I see in var&nbsp;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] ) &amp; 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 &amp; AP (MAT)] )
                )
            VAR _EPmax =
                SUMX (
                    FILTER ( Datatable, Datatable[Current Month] IN { _max } ),
                    SUM ( 'Datatable'[Diff in EP &amp; AP (MAT)] )
                )
            VAR EPAP =
                SUMX (
                    Datatable,
                    IF ( ISFILTERED ( Datatable[Current Month] ), _EPmax, _EPdefault )
                )
            VAR defmonth =
                VALUE (
                    MAX ( Datatable[File date].[Year] ) &amp; 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 ) &gt; 0,
                    0,
                    IF (
                        VALUE ( changeinmat ) &lt; 0,
                        0,
                        IF (
                            VALUE ( 0 - changeinmat )
                                &gt; ( VALUE ( EPAP ) ),
                            VALUE ( 0 - changeinmat ),
                            VALUE ( EPAP )
                        )
                    )
                )
    )
RETURN
    SUMX ( _VIRTUAL_TABLE, [Material Overrun] )
Best Regards,Rico Zhou
&nbsp;
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
&nbsp;

ck1995 · Answer

Anonymous&nbsp; 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&amp;year".&nbsp;&nbsp;I tried the solution , but the value returned is 0 throughout the table.&nbsp;&nbsp;&nbsp;

anonymous · Answer

Hi&nbsp;ck1995&nbsp;,
&nbsp;
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] ) )
            &amp; FORMAT ( EOMONTH ( TODAY (), -1 ), "mm" )
    )
VAR _max =
    CALCULATE (
        MAX ( ( 'Datatable'[Data file month&amp;year] ) ),
        ALLSELECTED ( 'Datatable'[Data file month&amp;year] )
    )
VAR _EPdefault =
    SUMX (
        FILTER ( 'Datatable', 'Datatable'[Data file month&amp;year] = _defaultmonth ),
        SUM ( 'Datatable'[Diff in EP &amp; AP (MAT)] )
    )
VAR _EPmax =
    SUMX (
        FILTER ( 'Datatable', 'Datatable'[Data file month&amp;year] IN { _max } ),
        SUM ( 'Datatable'[Diff in EP &amp; AP (MAT)] )
    )
VAR EPAP =
    SUMX (
        'Datatable',
        IF ( ISFILTERED ( 'Datatable'[Data file month&amp;year] ), _EPmax, _EPdefault )
    )
VAR defmonth =
    VALUE (
        YEAR ( MAX ( 'Datatable'[File date] ) )
            &amp; FORMAT ( EOMONTH ( TODAY (), -2 ), "mm" )
    )
VAR _minmnth =
    IF (
        ISFILTERED ( 'Datatable'[Data file month&amp;year] ),
        CALCULATE (
            MIN ( ( 'Datatable'[Data file month&amp;year] ) ),
            ALLSELECTED ( 'Datatable'[Data file month&amp;year] )
        ),
        defmonth
    )
VAR __BASELINE_VALUE =
    SUMX (
        FILTER ( 'Datatable', 'Datatable'[Data file month&amp;year] IN { _minmnth } ),
        SUM ( 'Datatable'[Actual cost(AP)] )
    )
VAR _maxmnth =
    IF ( ISFILTERED ( 'Datatable'[Data file month&amp;year] ), _max, _defaultmonth )
VAR __MEASURE_VALUE =
    SUMX (
        FILTER ( 'Datatable', 'Datatable'[Data file month&amp;year] IN { _maxmnth } ),
        SUM ( 'Datatable'[Actual cost(AP)] )
    )
VAR changeinmat = __MEASURE_VALUE - __BASELINE_VALUE
RETURN
    IF (
        VALUE ( EPAP ) &gt; 0,
        0,
        IF (
            VALUE ( changeinmat ) &lt; 0,
            0,
            IF (
                VALUE ( 0 - changeinmat )
                    &gt; ( 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&nbsp;show me more details about what result you want.&nbsp;
&nbsp;
&nbsp;Best Regards,Rico Zhou
&nbsp;
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

ck1995 · Answer

Hi&nbsp;Anonymous&nbsp;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

Forum Discussion

Incorrect Matrix visual subtotal

6 Replies

Related content

Customize subtotal in matrix

Matrix subtotal

Incorrect Column subtotals in matrix visual

Matrix Column Subtotal showing incorrect amount

Row Subtotals, Incorrect Sum in Matrix

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