The DAX code calculating the sum of maximum values

问题:

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.

Like,

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

How is it possible? Thanks in advance

---

Sample Data:

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

回答1:

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

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.

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.