0

I have a sample data set in Power BI. I was wondering if I could get a running total for Type A and adding it along with the original data?

I have an example.

Here is the data.

table1

I created a matrix table in Power BI to view the data like this:

table2

I was wondering if I could add the running total to the same matrix table, as well as having a different value for "Type". Something like this.

table4

Please let me know if this is possible. I know how to get the running total using the CALCULATE function but I don't know how to add it to the table along with the original data.

dee
  • 127
  • 3
  • 13
  • Does this answer your question? [DAX Running Total with Buckets](https://stackoverflow.com/questions/59547303/dax-running-total-with-buckets) – Alexis Olson Apr 01 '20 at 22:17
  • See this one as well: https://stackoverflow.com/questions/40226792/how-to-calculate-cumulative-total-and-in-dax – Alexis Olson Apr 01 '20 at 22:18
  • @AlexisOlson what about getting a running total for a certain "type" instead of everything? – dee Apr 02 '20 at 14:13
  • That's what the first link is about. – Alexis Olson Apr 02 '20 at 14:27
  • @AlexisOlson the first link doesn't say much besides instead of applying ALL, apply only the columns you need. Which doesn't show how? – dee Apr 02 '20 at 15:19
  • Your question is nearly identical. Just different column names. – Alexis Olson Apr 02 '20 at 15:24
  • @AlexisOlson yes, it's identical. But I'm asking based on a column, as well as a certain row. I want to get a running total based of the column "type", but only for type A (image above). I don't know if that is possible. – dee Apr 02 '20 at 16:27

1 Answers1

0

You can either tell it what row context to ignore:

RunningCount =
CALCULATE (
    SUM ( Table1[Count] ),
    FILTER (
        ALL ( Table1[Date] ),
        Table1[Date] <= MAX ( Table1[Date] )
    )

)

or you can tell it which row context to keep (removing all other context):

RunningCount =
CALCULATE (
    SUM ( Table1[Count] ),
    FILTER (
        ALLEXCEPT ( Table1, Table1[Type] ),
        Table1[Date] <= MAX ( Table1[Date] )
    )
)

These should give the same result in this case. I prefer the latter for this sort of thing in general since you're specifying what you're grouping over rather than what you don't want to keep in your grouping.

Alexis Olson
  • 38,724
  • 7
  • 42
  • 64