Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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.
Solved! Go to Solution.
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
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
Hi, it's work like a charm!
Thank so much!
Simple enough, but first rectify the date table,
Date = CALENDAR("1/1/2016", "12/31/2018")
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
@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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
111 | |
108 | |
108 | |
93 | |
61 |
User | Count |
---|---|
169 | |
138 | |
135 | |
102 | |
86 |