按日期分组并以不同方式过滤的不同列的累积总和 [英] Cumulative sum on different columns grouped by date and filtered differently

问题描述

我想根据三个不同的列创建三个累积总和列.每个累积和必须按如下方式计算:

• 应在dte列中按同一日期分组
• 在完成累积和运算之前，应先对 A 列进行 ASC 过滤
• B & 栏C 应该在累加运算之前过滤 DESC.

我应该创建 A、B、C 列相互分离的新表吗?

+------------+----+----------+---+----+-----+----------+----------+----------+|dte |编号 |last_dte |一个 |乙 |c |a_cumsum |b_cumsum |c_cumsum |+-----------+----+----------+---+----+-----+----------+------------+----------+|2018 年 12 月 4 日 |1 |12月3日|2 |8 |200 |2 ||||2018 年 12 月 4 日 |2 |12月3日|2 |5 |150 |4 ||||2018 年 12 月 4 日 |8 |12月3日|2 |25 |88 |6 ||||2018 年 12 月 4 日 |9 |12月3日|2 |89 |第456章8 ||||2018 年 12 月 3 日 |12 |12 月 2 日 |2 |1 |124 |2 ||||2018 年 12 月 3 日 |13 |12 月 2 日 |2 |5 |46 |4 ||||2018 年 12 月 3 日 |19 |12 月 2 日 |2 |22 |10 |6 |||+-----------+----+----------+---+----+-----+----------+------------+----------+

解决方案

这是

<小时>

请注意，这些是按 id 列(a_cum 的 ASC 以及 b_cum 和 c_cum).如果您需要按列的值而不是它们的 id 进行排序，那么我建议为每个列添加一个计算列，以便按您想要的方式对它们进行排名.然后在 DAX 中使用 rank 列而不是 id 列.

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

• should be grouped by the same date in the dte column
• column A should be filtered ASC before cumulative sum operation is done
• column B & C should be filtered DESC before cumulative sum operation.

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 |          |          |
+-----------+----+----------+---+----+-----+----------+----------+----------+

解决方案

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:

---

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.

这篇关于按日期分组并以不同方式过滤的不同列的累积总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！