cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## DAX measure: Sum where Fee Max value change for each date and customer

Hi, everyone
I need to know a DAX measure or formula to add only the maximum commission values (Fee) by date (Date) and customer (ID_Customer).
This is a small sample, the original table has more than 150 million of records with more than 40 fields, and I need a measure or expression in DAX to add, in this case, the maximum Fee numbers when Date (YYYYMM) and the account (ID_Customer) are the same.
Example: In the first case, we have Date = 201801, ID_Customer = 51223. There are four records meeting that condition, but the second is the one with the maximum Fee by 17, and which we have to select for the total sum. In the next case, for February we have five records meeting the condition, but the fifth is the one with the maximum Fee by 18, which we also have to select for the total sum. And so on with the other records.

I tried:
SUMMARIZE (Datos, Datos{Date], Datos[ID_Customer], "MaxFee", MAX (Datos[Fee])
but of course it did not work

Thank you so much
Datos Table
 Date ID_Customer Fee 201801 521223 6 201802 521223 6 201802 521223 14 201802 521223 5 201802 521223 7 201802 521223 18 201803 521223 4 201803 521223 16 201803 521223 2 201801 1026532 20 201801 1026532 6 201801 1026532 16 201802 1026532 18 201802 1026532 18 201802 1026532 12 201802 1026532 8 201803 1026532 17 201803 1026532 19
1 ACCEPTED SOLUTION
Super User
``````Sum of Max Fee by Date and ID =
SUMX (
SUMMARIZE ( 'Table', 'Table'[ID_Customer], 'Table'[Date] ),
CALCULATE ( MAX ( 'Table'[Fee] ) )
)``````

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

Proud to be a Super User!

6 REPLIES 6
Super User
``````Sum of Max Fee by Date and ID =
SUMX (
SUMMARIZE ( 'Table', 'Table'[ID_Customer], 'Table'[Date] ),
CALCULATE ( MAX ( 'Table'[Fee] ) )
)``````

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

Proud to be a Super User!

Helper I

Thank you very much Nathaniel, I'm so hapy.

You don't know how to serve me.

Thank you very much again

Super User

Hi @Guille2711410 , you are welcome!
Nathaniel

Proud to be a Super User!

Super User

Hi @Guille2711410 ,
In the first case, we have Date = 201801, ID_Customer = 51223. There is no customer with that ID. Can you post a pbix? And what your expected outcome is?

Nathaniel

Proud to be a Super User!

Helper I

Ohh, your absolutily right, the ID_Customer is 521223.

I'm new, I don't understand how to post PBIX.

I hope to get the sum of all the maximums according to the criteria described.

Thank you very much.

Helper I

Hi Nathaniel_C

I would very much apreciate helping you with this consultation, it's very urgent.

I can be a great promoter of Power BI in the company where I work.

Thank you very much.