按日期分组并以不同方式过滤的不同列上的累积总和 [英] Cumulative sum on different columns grouped by date and filtered differently
问题描述
我想基于三个不同的列创建三个累积总和列.每个累积总和必须如下计算:
- 应在
dte
列中按同一日期分组 - 在完成累加和运算之前,应对列A进行ASC过滤
- 列B&累积和运算之前,应对C进行DESC过滤.
我应该创建彼此分开的列A,B,C的新表吗?
+ ----------- + ---- + ---------- + --- + ---- + ----- +---------- + ---------- + ---------- +|dte |id |last_dte |一个|b |c |a_cumsum |b_cumsum |c_cumsum |+ ----------- + ---- + ---------- + --- + ---- + ----- + ---------- + ---------- + ---------- +|2018年12月4日|1 |3月12日|2 |8 |200 |2 ||||2018年12月4日|2 |3月12日|2 |5 |150 |4 ||||2018年12月4日|8 |3月12日|2 |25 |88 |6 ||||2018年12月4日|9 |3月12日|2 |89 |456 |8 ||||12/3/2018 |12 |2月2日|2 |1 |124 |2 ||||12/3/2018 |13 |2月2日|2 |5 |46 |4 ||||12/3/2018 |19 |2月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屋!