Find the maximum value of the total (sum of the all entries) by day

[smquerido]

Hi,

When i am using 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 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 in advance!

Best regards.

[mangaus1111 · Accepted solution]

Hi @smquerido ,

maybe I have not well undestood your expected result, but if you need the Max Total Distance by Day, then you have to use the below measure:

Max Total Distance by Day =

MAXX(

     SUMMARIZE(

                'Table',

                'Table'[Data],

                'Table'[Athlete]

              ),

    CALCULATE(SUM('Table'[Total Distance]))

)

See the pbi file here:

https://1drv.ms/u/s!Aj45jbu0mDVJiHCpXnEe07hnc3Hf?e=7hye5B

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

[smquerido]

@mangaus1111 thank you very much. Your solution helped me to understand the SUMMARIZE funtion and solved the problem. 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]))

[mangaus1111]

Hi @smquerido ,

try to use this:

=
CALCULATE (
CALCULATE (
MAX ( Folha1[Total Distance] ),
FILTER ( FOLHA1, Folha1[Typology] = "Match" )
),
ALL ( Folha1[Data] ),
REMOVEFILTERS ( Folha1[Exercise] )
)

[smquerido]

@mangaus1111  thanks for your solution but the results are the same of mine, it does not sum all the exercises of the day (warm-up, first half and second half) and returns the maximum value found just for one exercise (p.e., for Athlete Denis it returns 2683 instead of 6804.