Row based running total

[Anonymous]

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

[amitchandak · Accepted 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

[v-jinweili-msft · Accepted solution]

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.

[amitchandak]

@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])

[Anonymous]

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)

[amitchandak · Accepted 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