Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
SuperSayan
Resolver I
Resolver I

sum of max values by date/client

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

 

Max value Issue.JPG

 

1 ACCEPTED SOLUTION
OwenAuger
Super User
Super User

@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 🙂


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

3 REPLIES 3
OwenAuger
Super User
Super User

@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 🙂


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

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.

Graph - Max value Issue.JPGData - Max value Issue.JPG

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.

 

 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.