DAX代码计算每天的最大值之和 [英] The DAX code calculating the sum of maximum values per day

问题描述

我想在Power BI中编写一个dax代码，该代码计算每天的最大值之和。在第一列中有生产数据，在第二列中有日期和时间。计数器在每天开始时重置。我想获取每天的最大值并将其汇总。

I would like to write a dax code in Power BI which calculate the sum of maximum values of each day. In one column there is a production data and on the second column there is the the date and time. The counter resets in the beginning of every day. I want to pick up the maximum value for each day and sum them up.

就像

总和=第1天的最大值+第2天的最大值+ .......第N天的最大值

Sum = Max of Day1 + Max of Day 2 + ....... Max of Day N

怎么可能？
预先感谢

How is it possible? Thanks in advance

样本数据：

Date                    Daily Counter
2/1/2018 12:00:00 AM    1
2/1/2018 6:00:00 AM     2
2/1/2018 12:00:00 PM    3
2/1/2018 6:00:00 PM     4.5
2/2/2018 12:00:00 AM    1
2/2/2018 6:00:00 AM     3
2/2/2018 12:00:00 PM    6
2/2/2018 6:00:00 PM     9
2/3/2018 12:00:00 AM    5
2/3/2018 6:00:00 AM     6
2/3/2018 12:00:00 PM    12
2/3/2018 6:00:00 PM     18

推荐答案

这样会容易一些如果您有一个仅包含日期而不是日期时间值的列。因此，首先，创建一个计算列（我假设您的表名为 Data ）：

This will be a bit easier if you have a column that has just dates rather than datetime values. So first, create a calculated column (I'm assuming your table is called Data):

DateDay = DATEVALUE(Data[Date])

现在我们有了，

MaxValue =
SUMX (
    SUMMARIZE ( Data, Data[DateDay], "MaxCount", MAX ( Data[Daily Counter] ) ),
    [MaxCount]
)

它的作用是创建一个表，该表通过获取每天的最大计数来汇总每天。 SUMX 然后遍历摘要表的每一行，并累加每天的最大计数。

What this does is create a table that summarizes each day by taking the maximum count on each day. The SUMX then goes through each row in the summary table and adds up the maximum count for each day.

注意自从 Data 表传递到 SUMMARIZE 通过其评估上下文进行过滤，因此 DateDay 过滤器得以保留。

Note that this works not just for the total, but on each row in your visual as well since the Data table that gets passed into the SUMMARIZE is filtered by its evaluation context so the DateDay filter is preserved.

这篇关于DAX代码计算每天的最大值之和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！