Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I have a table /matrix where i can show running total measure in a column but i would like to show it as row based in a martix. As a example:
Month | A | B |
Jan | 3 | 5 |
Total | ?( running total till January) | ?( running total till January) |
Feb | 5 | 7 |
total | ?( running total till February) | ?( running total till February) |
March | 6 | 8 |
Thanks!
BR
Agreena
Solved! Go to Solution.
@Anonymous , If you use only a month, you will not get the total. Use two measures profit and Running total profit
Cumm Sales = CALCULATE(SUM(Table[Profit]),filter(allselected('Date'),'Date'[date] <=max('Date'[date])))
Use date table, take month year from date table.
Enable Show on Row in matrix
Hi @Anonymous ,
1. To add the “Total” rows , please create a new table:
Table 2 =
VAR _T1 =
ADDCOLUMNS (
DISTINCT ( 'Table'[Month] ),
"Index", MONTH ( CONVERT ( [Month] & " 1", DATETIME ) )
)
VAR _T2 =
CROSSJOIN ( ROW ( "Month", "Total" ), { 1, 2 } )
RETURN
UNION ( _T1, _T2 )
2.Since there is only month name in your original table, please firstly extract Month Number ,which is used to compare.
Month Number =
MONTH ( CONVERT ( MAX ('Table' [Month] ) & " 1", DATETIME ) )
3.To match the value of “Month” and “Total”, please try:
Measure =
SWITCH (
MAX ( 'Table 2'[Month] ),
"Total",
CALCULATE (
SUM ( 'Table'[Profit] ),
FILTER ( 'Table', [Month Number] <= MAX ( 'Table 2'[Index] ) )
),
CALCULATE (
SUM ( 'Table'[Profit] ),
FILTER ( 'Table', [Month] = MAX ( 'Table 2'[Month] ) )
)
)
Output:
Best Regards,
Jinwei Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
1. To add the “Total” rows , please create a new table:
Table 2 =
VAR _T1 =
ADDCOLUMNS (
DISTINCT ( 'Table'[Month] ),
"Index", MONTH ( CONVERT ( [Month] & " 1", DATETIME ) )
)
VAR _T2 =
CROSSJOIN ( ROW ( "Month", "Total" ), { 1, 2 } )
RETURN
UNION ( _T1, _T2 )
2.Since there is only month name in your original table, please firstly extract Month Number ,which is used to compare.
Month Number =
MONTH ( CONVERT ( MAX ('Table' [Month] ) & " 1", DATETIME ) )
3.To match the value of “Month” and “Total”, please try:
Measure =
SWITCH (
MAX ( 'Table 2'[Month] ),
"Total",
CALCULATE (
SUM ( 'Table'[Profit] ),
FILTER ( 'Table', [Month Number] <= MAX ( 'Table 2'[Index] ) )
),
CALCULATE (
SUM ( 'Table'[Profit] ),
FILTER ( 'Table', [Month] = MAX ( 'Table 2'[Month] ) )
)
)
Output:
Best Regards,
Jinwei Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Anonymous , if you running across A and B value
sumx(filter(allselected(Table), Table[Month Year] = max(Table[Month Year]) && Table[Column] <= max(Table[Column]) ), Table[Value])
HI Amitchandak,
Thanks. Here runing total is not across two column, its based on per column. please just consider only one column (Profit)
Month | Profit |
Jan | 4 |
Total | ? (runing total til Jan) |
Feb | 7 |
Total | ?( running total till February) |
@Anonymous , If you use only a month, you will not get the total. Use two measures profit and Running total profit
Cumm Sales = CALCULATE(SUM(Table[Profit]),filter(allselected('Date'),'Date'[date] <=max('Date'[date])))
Use date table, take month year from date table.
Enable Show on Row in matrix
User | Count |
---|---|
57 | |
22 | |
21 | |
19 | |
16 |
User | Count |
---|---|
87 | |
87 | |
52 | |
37 | |
23 |