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

这怎么可能?提前致谢

样本数据:

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])

现在我们有了，让我们编写度量.

Now that we have that, let's write the measure.

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.

请注意，这不仅适用于总数，还适用于视觉中的每一行，因为传递到 SUMMARIZE 的 Data 表是由其过滤的评估上下文，因此 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屋！