DAX Formula for running total BUT only for a certain column

Question

I've managed to get my running total working for a date when using all values in a column, and I have a date table to table relationship setup as well. My issue goes a bit more into getting it to work for only specified column values of my main table. I can't provide the actual table due to confidentiality however I can provide an example of what its like.

My table currently has multiple useless columns in regards to the running total. It however is based of a Date that I have setup. There is another column with values a, b, c in it. I only want to run a running total of value b. However I'm stuck only able to run a running total for all rows regardless of the value in the column containing a,b,c. My title for that column is type and the value for my running total column is Cumulative which runs off a measure calculating a total from an Amount Column by date.

I attempted to create a whole name table using a

RnTotal = Filter(info[Type] = "b")

This gives me only the information I want to total, but when I use the formula;

Cumulative = 
Calculate (
Sum([Amount]),
Filter(All(RnTotal),RnTotal[Date]<=MAX(DateTable[Date])
)
)

It has a running total count that just doesn't make sense and makes me assume its also adding a and c in even though they aren't showing on the new table.

I'm not an expert in DAX and just trying to learn so any help to get through this little speed bump would be appreciated.

Alexis Olson Alexis Olson · Accepted Answer · 2018-09-05T19:30:10

You can use multiple filters inside a CALCULATE function.

Cumulative = 
    CALCULATE (
        SUM( [Amount] ),
        FILTER(
            All( RnTotal ),
            RnTotal[Date] <= MAX( DateTable[Date] )
        ),
        info[Type] = "b"
    )

DAX Formula for running total BUT only for a certain column

1 Answers