Forum Discussion

ck1995's avatar
ck1995
Helper I
4 years ago

Incorrect Matrix visual subtotal

  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 i...
Need Help
Show and Tell
Anonymous's avatar
Anonymous
Not applicable
4 years ago

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's avatar
ck1995
Helper I
4 years ago

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's avatar
    Anonymous
    Not applicable
    4 years ago

    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.