Forum Discussion
marcio_fornari
Resolver I
4 years agoAdd 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...
- 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
Anonymous
4 years agoNot 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
4 years agoHi, it's work like a charm!
Thank so much!