Find the MAX value ever of SUM of multiple entries for each day

[smquerido]

Hi,

In all "Match Day" conditions i want to find the MAX value of "Total Distance" achived in one day. However, for each Day i have at least two entries (each Half of the match) and i need to first SUM the entries for each day. With the following formula it is just findind the MAX value covered in one half (one entry) and it is not considering the SUM of muliple entries of each day. How can i modificate the forumula to solve the issue?

REF_TOTAL DISTANCE = CALCULATE(CALCULATE(

    MAX(Folha1[Total Distance]),

    FILTER(FOLHA1, Folha1[MD]="Match Day")),

    ALL(Folha1[Data]))

Thanks!

[smquerido]

@v-yueyunzh-msft thank you for your reply and sorry for the confusion. I attache an image to better explain the situation. I can't share the entire sample data because has some sensitive information but with the image i think it will be easier. 

When i use the following formula it returns the highest value found in the day [as in red rectangles (1)]. It is ok if we have just one entrie in the day for each athlete.

REF_TOTAL DISTANCE = CALCULATE(CALCULATE(

    MAX(Folha1[Total Distance]),

    FILTER(FOLHA1, Folha1[Typology]="Match")),

    ALL(Folha1[Data]))

However, in this case we have 3 entries (warm-up, 1st half and 2nd half) in the day for each athlete and i need to find in the entire sample data the highest values (SUM of the 3 entries) with date as reference. Supposing that the maximum values found is in day 28/10/20 (as in the image) it should return the sum of Total Distance variable as in green retagle (2). What is the best way to solve the issue?

Thanks again.

Best regards.

SQ

[v-yueyunzh-msft]

Hi , @smquerido 

It can be realized in Power Query Editor, Here are the steps you can refer to :
(1)This is my test data:

(2)We can put this in the "Advanced Editor":

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTQN7LQNzIwMlLSUfJNLEnOANIuqXmZxUC6PLEoV7e0AMgyVorVIaA4ILGoRMEQyDAiUq0RfnO98lNzUNxgQlAt3AmmxCk1wu/aoPyUosz0fBRHGBKjnJigQFMNkjKDq7YkHCOGRoRVY3EHfrVGEB/GAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Typology = _t, Athelete = _t, Exercise = _t, #"Total Distance" = _t]),
    test = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Typology", type text}, {"Athelete", type text}, {"Exercise", type text}, {"Total Distance", Int64.Type}}),
    Custom1 = Table.Group(test,{"Date","Athelete"},{"test",(x)=> Table.AddColumn(x,"total",(y)=>List.Sum( x[Total Distance])   )      }   ),
    #"Expanded test" = Table.ExpandTableColumn(Custom1, "test", {"Typology", "Exercise", "Total Distance", "total"}, {"Typology", "Exercise", "Total Distance", "total"})
in
    #"Expanded test"

(3)The result is as follows:

(4)Then we can find the max number quickly i think.

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[smquerido · Accepted solution]

@v-yueyunzh-msft , thank you very much for your effort and availability. I found a suggestion in other topic to use SUMMARIZE function and it solved the problem after a lot of attemps. Here the entire measure:

REF_TOTAL DISTANCE = CALCULATE(CALCULATE(MAXX(SUMMARIZE (BD, BD[Date], BD[Typology]), CALCULATE(SUM(BD[Total Distance])

)), FILTER(BD,BD[Typology]="Match")), ALLEXCEPT(BD,BD[Athlete]))

[v-yueyunzh-msft]

Hi , @smquerido 

According to your description , I have some questions about your need:

(1)What is "for each Day i have at least two entries"? Two entries are if they are represented in the table?

(2)Can you provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly