Forum Discussion

ck1995's avatar
ck1995
Icon for Helper I rankHelper 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 ,

 

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

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'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
      Icon for Helper I rankHelper 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.