Forum Discussion

marcio_fornari's avatar
marcio_fornari
Icon for Resolver I rankResolver I
4 years ago
Solved

Add new row with accumulate in Matrix

Hi,  It's possible add a new a row in Matrix with the Month Accumulate?  I have the total and i would like a new row with the accumulate.  Like this example:   My Example file: Example.pb...
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi marcio_fornari ,

    I updated your sample pbix file(see attachment), please check whether that is what you want.

    1. Create a calculated table with below formula

    Table = UNION(VALUES('Sales'[Product]),ROW("Product","XMonth Accumulate"))

    2. Create two measures as below to get the sum of sales

    Measure = 
    VAR _selmonthname =
        SELECTEDVALUE ( 'Date'[MontName] )
    VAR _selmonthnum =
        CALCULATE (
            MAX ( 'Date'[MontNumber] ),
            FILTER ( 'Date', 'Date'[MontName] = _selmonthname )
        )
    RETURN
        SWITCH (
            SELECTEDVALUE ( 'Table'[Product] ),
            "XMonth Accumulate",
                SUMX (
                    FILTER (
                        ALLSELECTED ( 'Sales' ),
                        MONTH ( 'Sales'[Date] ) <= VALUE ( _selmonthnum )
                    ),
                    [Sales (Selected Month)]
                ),
            SUMX (
                FILTER ( 'Sales', 'Sales'[Product] = SELECTEDVALUE ( 'Table'[Product] ) ),
                [Sales (Selected Month)]
            )
        )
    Month Accumulate = 
    IF (
        ISINSCOPE ( 'Table'[Product] ),
        SUMX ( VALUES ( 'Table'[Product] ), [Measure] ),
        [Sales (Selected Month)]
    )

    3. Create a matrix visual (Rows: field Product of Table   Column: field MonthName in Date table  Values: [Month Accumulate] )

    Best Regards