Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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]))
Solved! Go to Solution.
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 🙂
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 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.
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.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
90 | |
84 | |
70 | |
49 |
User | Count |
---|---|
143 | |
121 | |
112 | |
59 | |
58 |