CODEWITHSUNDEEP
powerbidax
Roger Steinberg
Roger Steinberg

Reputation: 1604

Cumulative sum on different columns grouped by date and filtered differently

I would like to create three cumulative sum columns based on three different column. Each cumulative sum must be calculated as follows:

Should I create new tables with columns A, B, C separated from each other?

+-----------+----+----------+---+----+-----+----------+----------+----------+
|    dte    | id | last_dte | a | b  |  c  | a_cumsum | b_cumsum | c_cumsum |
+-----------+----+----------+---+----+-----+----------+----------+----------+
| 12/4/2018 |  1 | 3-Dec    | 2 |  8 | 200 |        2 |          |          |
| 12/4/2018 |  2 | 3-Dec    | 2 |  5 | 150 |        4 |          |          |
| 12/4/2018 |  8 | 3-Dec    | 2 | 25 |  88 |        6 |          |          |
| 12/4/2018 |  9 | 3-Dec    | 2 | 89 | 456 |        8 |          |          |
| 12/3/2018 | 12 | 2-Dec    | 2 |  1 | 124 |        2 |          |          |
| 12/3/2018 | 13 | 2-Dec    | 2 |  5 |  46 |        4 |          |          |
| 12/3/2018 | 19 | 2-Dec    | 2 | 22 |  10 |        6 |          |          |
+-----------+----+----------+---+----+-----+----------+----------+----------+

Upvotes: 1

Views: 735

Answers (1)

Alexis Olson
Alexis Olson

Reputation: 40204

This is a classic example of a Cumulative Sum DAX Pattern.

You do not need separate tables.

As a calculated column

a_cum = 
VAR CurrentID = [id]
RETURN
    CALCULATE (
        SUM ( Table01[a] ),
        FILTER (
            ALLEXCEPT ( Table01, Table01[dte] ),
            Table01[id] <= CurrentID
        )
    )

The b_cum and c_cum columns are analogous. Just switch out the column you're referencing and change the direction of the inequality for DESC instead of ASC. For example,

b_cum = 
VAR CurrentID = [id]
RETURN
    CALCULATE (
        SUM ( Table01[b] ),
        FILTER (
            ALLEXCEPT ( Table01, Table01[dte] ),
            Table01[id] >= CurrentID
        )
    )

This should give you a table like this:

Calculated Columns

Note that these are ordering the cumulative sum by the id column (ASC for a_cum and descending for b_cum and c_cum). If you need to sort by the values of the columns rather than their id, then I'd suggest adding a calculated column for each to rank them how you want. Then use the rank column instead of the id column in your DAX.

Upvotes: 2

Related Questions