sum of max values by date/client

[SuperSayan]

Hi Everyone,

I looked onto the forum but didn't find exactly what I'm trying to achieve.

I have a long table of intervention done by technicians with date, client reference etc.

Each intervention has a price.

I have created the measure that allows me to sum the prices and divide it by the number of days where the technician has made at least 1 intervention.

However, the last step for me is to count only the highest price by day by client reference. Indeed, when a technician goes to 1 client and does 2 or 3 interventions at the same time, I want to count only the highest price in my calculation.

See below an example of the data.

In this example, for eache tech I want to sum "colonne1" but only the max value by day and client (Code Implantation column).

In the below, there is only 1 tech (Tech1) who has done 3 interventions on the same client on the same day and in this case I want to sum only the max (20 on the row 51203 and ignore the 2 times 5 on rows 51202 and 51204).

I have tried this based on some stuff I've seen online but it doesn't do the trick:

sumx(ALLEXCEPT(Feuil1;Feuil1[FinDate];Feuil1[Code implantation]);MAX(Feuil1[Colonne1]))

[OwenAuger · Accepted solution]

@SuperSayan

I would personally write something like:

Sum of Max Colonne1 by day and client =
SUMX (
    SUMMARIZE ( Feuil1; Feuil1[FinDate]; Feuil1[Code implantation] );
    CALCULATE ( MAX ( Feuil1[Colonne1] ) )
)

SUMMARIZE(...) creates a table containing unique existing combinations of date/client. SUMX then sums the max value of Colonne1 for each of those combinations.

Owen 🙂

[SuperSayan]

Owen Thanks a lot for your proposal.

I'm a bit confused with the results it gives me as it seems to be working, the values are somewhat in the expected range but if I take an exemple on 1 technician on 1 date, I am not getting the correct value.

But maybe the way I use it on my report isn't correct.

This is one of the main issue I have with PowerBI as I am quite new and unexperimented on it.
When I create a measure, how can I check this is actually computing what I wanted? Because when I put the measure in a report like a bar chart in this example, I'm not able (or at least I don't know how) to check the calculation steps or come back to the actual data that gives this result.

See below how I'm presenting it for a said technician on the a specific date and what I have in my data model ( 267,2 in the report while in my dataset I should add all the value but the last one which gives me 353,1). I'm checking that on my Excel data as I'm not sure how to navigate in the data in PowerBI as the "Colonne1" is actually a calculated column.

[SuperSayan]

Owen please disregard my previous message, I'm not sure what happened, I refreshed the data and now I have the correct result!

Thanks so much for your help, swift answer and with a great explanation so I understand it and can actually reuse it somewhere else.