Forum Discussion
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:
Thank you.
- Anonymous4 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
4 Replies
- AnonymousNot applicable
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
- marcio_fornari
Resolver I
Hi, it's work like a charm!
Thank so much!
- amitchandak
Super User
marcio_fornari , You may have to create a custom table with that data to get this
refer
https://community.powerbi.com/t5/Desktop/Calculated-Row-in-Matrix/m-p/249519
- CNENFRNL
Community Champion
Simple enough, but first rectify the date table,
Date = CALENDAR("1/1/2016", "12/31/2018")