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
K-LED
Helper I
Helper I

Calculated max value by using group by for multiple columns

Hi,

I'm trying to calculate the max value by using group by for multiple columns.

 

I Have this table :

21-10-2019 17-14-36.png

 

What  I want to do is to calculate the max amount for each "Id offer" by selecting the "Rev" with the maximum amount for each "Id client"  as below :

21-10-2019 17-15-27.png

 

If the offer has multiple "Id client" , in this case selecting the "Id client" column with the max value as below :

21-10-2019 17-15-55.png

 

I'm trying this but it doesn't work :

Max amount = GROUPBY( 
            ADDCOLUMNS(
                GROUPBY(
                    Tableau_Quotation,
                    'Tableau_Quotation'[Document Nr],
                    'Tableau_Quotation'[Rev],
                ),
                "Max Count", CALCULATE(MAX(Tableau_Quotation[Offer amount EUR]))
            ),
            'Tableau_Quotation'[Document Nr],
            "Max", MAXX(CURRENTGROUP(), [Max Count]))

How I can fix that with dax ?

 

Thanks in advance

1 ACCEPTED SOLUTION
Nathaniel_C
Super User
Super User

@K-LED ,

If I understand this correctly, you are creating a new table. I used SUMMARIZE and LOOKUPVALUE

new = SUMMARIZE('Table','Table'[ID Offer],'Table'[ID Client],"AMT",max('Table'[Amount])),
==
Lookup = LOOKUPVALUE('Table'[Rev],'Table'[Amount],new[AMT],'Table'[ID Offer],new[ID Offer],'Table'[ID Client],new[ID Client])



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.
NathanielSUMMARIZE.PNG

 

 Will post pbix shortly.





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

@K-LED ,

If I understand this correctly, you are creating a new table. I used SUMMARIZE and LOOKUPVALUE

new = SUMMARIZE('Table','Table'[ID Offer],'Table'[ID Client],"AMT",max('Table'[Amount])),
==
Lookup = LOOKUPVALUE('Table'[Rev],'Table'[Amount],new[AMT],'Table'[ID Offer],new[ID Offer],'Table'[ID Client],new[ID Client])



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.
NathanielSUMMARIZE.PNG

 

 Will post pbix shortly.





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

Proud to be a Super User!




Hi,

Thanks for your response @Nathaniel_C ,

 

If it's possible I'd like to not create a new table but creating a new measure at the same table.

Otherwise with your solution you miss the last step by selecting the "Id offer" with the max "AMT":

   Id Offer | ID Client | AMT | Lookup

        1      |       B       |  150 |     2

 

Can we use the same functions without creating a new table but stay with the same table ?

 

Thnak you again for your answer!

Hi @K-LED ,

Maybe I am not seeing for what you are looking.
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

rev1.PNG

 

 

Aren't these the same?rev.PNG





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

Proud to be a Super User!




Hii,

 

I added a step to select the line with the max amount...

Thank you again for your answer

 

Regards.

You are welcome!





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

Proud to be a Super User!




PBIX file PBIX 





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

Proud to be a Super User!




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.

Top Kudoed Authors