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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Guille2711410
Helper I
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
DateID_CustomerFee
2018015212236
2018025212236
20180252122314
2018025212235
2018025212237
20180252122318
2018035212234
20180352122316
2018035212232
201801102653220
20180110265326
201801102653216
201802102653218
201802102653218
201802102653212
20180210265328
201803102653217
201803102653219
1 ACCEPTED SOLUTION
Nathaniel_C
Super User
Super User

Hi @Guille2711410 ,

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

sum of max.PNG

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

6 REPLIES 6
Nathaniel_C
Super User
Super User

Hi @Guille2711410 ,

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

sum of max.PNG

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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

You don't know how to serve me.

Thank you very much again

 

Hi @Guille2711410 , you are welcome!
Nathaniel





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Nathaniel_C
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?
Please read this post to get your question answered more quickly:

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
Nathaniel





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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.

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.

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors