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...
Need Help
  • 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